CODEWITHSUNDEEP
sqlsql-server-2008
Joe Defill
Joe Defill

Reputation: 499

SQL - Summing/counting rows based on matching columns

I have the 2 following tables

Tracking

tracking_id  item_extension  quantity
a            144             100
b            144             200
c            250             150

Account

tracking_id  account
a            999
b            999
c            999

Here's my query -

SELECT sum(qty) as qty, count(item_extension) as total, t.tracking_id, item_extension, account
FROM Tracking t
INNER JOIN Account a ON t.tracking_id = a.tracking_id
GROUP BY t.tracking_id, item_extension, account

What I want to happen here is get count of item_extension and sum of quantity based on matching account/item_extension fields. So because there are 2 rows with matching account and item_extension fields, it should sum up 2 of them like so:

qty  total  tracking_id  item_extension  account
300  2      a            144             999
300  2      b            144             999
150  1      c            250             999

Instead I get this result:

qty  total  tracking_id  item_extension  account
100  1      a            144             999
200  1      b            144             999
150  1      c            250             999

Is there a good way of doing this?

Upvotes: 0

Views: 22

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You want to count item_extension values that are not in the current row. So, use window functions. I think this does what you want:

SELECT sum(qty) as qty,
       sum(count(*)) over (partition by item_extension) as total,
       t.tracking_id, item_extension, account
FROM Tracking t
INNER JOIN Account a ON t.tracking_id = a.tracking_id
GROUP BY t.tracking_id, item_extension, account;

Upvotes: 1

Related Questions