Difficulty with multiple sums for different ID's

[jameslivsey]

Hi,

I'm having trouble writing a stored procedure in MS SQL SERVER. I have a table with 3 columns - REFERENCE, ID and AMOUNT.

What I'm trying to do is SUM the AMOUNT based on different ID's for each REFERENCE. So it would be something like

SUM(TBL.AMOUNT) WHERE ID IN '020'. '021', '022' AND REFERENCE = '001'

SUM(TBL.AMOUNT) WHERE ID IN '023', '024'' '025' AND REFERENCE = '001'

SUM(TBL.AMOUNT) WHERE ID IN '020'. '021', '022' AND REFERENCE = '002'

SUM(TBL.AMOUNT) WHERE ID IN '023', '024'' '025' AND REFERENCE = '002'

and so on (although there are many more combinations of id's and references)

Initially I went for a cursor to iterate through all the references in the table and do all the sums based on each of the id's but that didnt work, possibly becasue i was using a cursor, it just ran and ran. So I was hoping to find firstly a way that worked and secondly a more set based way of doing it.

Can anyone help?

Many thanks

[gusano79]

Best to avoid cursors when possible (which is almost always :).

Here's a start, to sum Amounts per unique Reference and ID:

SELECT
    Reference,
    ID,
    SUM(Amount) AS TotalAmount
FROM [table]
GROUP BY
    Reference,
    ID

What is the purpose of the ID grouping?

[jameslivsey]

Hi gusano79, thanks for replying.

The Id represents a certain class of amount so in a money/spending context groceries might be ID 1,3 & 4 entertainment might be ID 2, 6 & 7 etc.

Then the Reference field would be the person. So for each person I would need to sum the amounts for each class of spending (and then update a secondary table but thats a separate issue).

For each person there are 9 separate spending classes. The fact that I need to apply those 9 classes to each person reference was what drew me to a cursor but I realise they arent the best way to work.

hope that makes sense..

[gusano79]

So we're missing data to indicate the spending class of a particular ID. Are you limited to this single table as described, or can we modify things a bit?

[jameslivsey]

No, I think I can do it however, as long as I end up with the correct data in the correct places. Thanks again!

[gusano79]

Okay. If I were doing this, I'd end up with four tables: one each for spending classes, items available for purchase, persons, and actual purchases. The "amount per spending class" sproc would start with the purchases table and join to the others as needed.

Try modeling it that way and see how far you get; I've left plenty as an "exercise for the reader."