How to sum over distinct values of two columns

Question

Post reply

How to sum over distinct values of two columns

SQLUserC

SSCrazy

Points: 2345
More actions
August 3, 2012 at 2:51 pm

#279823

Hey all,
I have a situation where I have some query results that contain a tranasaction ID, an item number, and a fee.
I want to simply add up all the charges of the distinct values of the transaction ID and item number. There's a catch. Due to the way that these query results were constructed, there can be transactions and items listed multiple times in the table. So, for instance, this might appear in the table:
OrderNumber|| Item || Fee
523 || Banana || $5
52 || Apple || $3
523 || Banana || $5
In this small example, I would only want to count the banana bought in order 523 as $5, not $10.
Assume that an item can only appear once in a given order.
How do I go about doing this? The only way I can think of doing this is to create a sort of composite key by combining OrderNumber and Item into one field. In this way, I could select distinct on that combined column and avoid duplicates.
There are a couple of issues with this. First, I figure there has to be a better way. I've been working with SQL for a while but my knowledge of aggregating and joining is still pretty shaky.
Second, even if I wanted to do it my way, how exactly would I create the combined column if the OrderNumber field is actually a GUID?
I would greatly appreciate any advice or insights anyone can provide.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the real data might be a little more complex, but doesn't a simple group by get you the data you asked for?

/*

OrderNumber Item Fee

----------- ------ -----------

52 Apple 3

523 Banana 5

*/

With MyCTE (OrderNumber,Item,Fee)

AS

(

SELECT '523','Banana',5 UNION ALL

SELECT '52','Apple',3 UNION ALL

SELECT '523','Banana',5

)

SELECT OrderNumber,Item,Fee

FROM MyCTE

GROUP BY

OrderNumber,Item,Fee

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLUserC SSCrazy Points: 2345 More actions · Answer 2

Ah, yes. How obvious. Sorry about that.

OK, now as for summing across all charges, I'm planning on selecting the resulting grouped records into another table and then using that to sum. Is there a better way?