Member Avatar for 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

  • 2 Contributors
  • 5 Replies
  • 220 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by gusano79

Dani AI

Generated

Building on 's set-based direction and 's need to total multiple ID groups per person, the best long-term approach is to make the ID→class relationship data-driven and avoid row-by-row logic. Put ID→class mappings in a small lookup table, aggregate with joins and GROUP BY (or conditional aggregation/PIVOT), then update the summary table in one set-based statement. This is far faster and easier to maintain than a cursor.

Example: create a mapping table and produce totals per person/class.

-- map each ID to a spending class
CREATE TABLE SpendingClassMap (ID VARCHAR(20) PRIMARY KEY, ClassName VARCHAR(50));

-- totals per person per class
SELECT p.Reference, m.ClassName, SUM(p.Amount) AS TotalAmount
FROM Purchases p
JOIN SpendingClassMap m ON p.ID = m.ID
GROUP BY p.Reference, m.ClassName;

If you need one row per person with a column per class, use conditional aggregation (or PIVOT). That keeps the logic in SQL and avoids hard-coded IN lists inside the procedure.

SELECT p.Reference,
  SUM(CASE WHEN m.ClassName = 'Groceries' THEN p.Amount ELSE 0 END) AS GroceriesTotal,
  SUM(CASE WHEN m.ClassName = 'Entertainment' THEN p.Amount ELSE 0 END) AS EntertainmentTotal
FROM Purchases p
JOIN SpendingClassMap m ON p.ID = m.ID
GROUP BY p.Reference;

To populate or refresh the secondary summary table, use a single MERGE (or UPDATE ... FROM) driven by the aggregated result set rather than looping. Performance and correctness tips: index Purchases(Reference, ID) and SpendingClassMap(ID), use DECIMAL for money, update statistics, batch large MERGE operations, and validate with the execution plan. If class membership changes often, keep it in the mapping table so the sproc need not be edited each time.

Recommended Answers

All 5 Replies

Member Avatar for 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?

Member Avatar for 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..

Member Avatar for 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?

Edited by gusano79
Member Avatar for 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!

Member Avatar for 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."

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.