CODEWITHSUNDEEP
sqlt-sqlsum
John
John

Reputation: 3677

How to Sum up fields across different groups in T-SQL

I have a bunch of tables that I left join and a Group By clause that groups a bunch of columns.

However, there is a one column that is group-by'ed on but remains distinct (productNumber).

I need to sum up the quantity column below:

salesID  historyID  productID  name  productNumber  quantity
      1        123          1     A         234554        10
      1        123          1     A         666666        10

I want only the first record but with the quantity of 10+10=20.

The first record would have a flag mainNumber = 1 and the second record would have a mainNumber=0, however that column does not appear in the SELECT.

In other words, I'd like to sum up the quantities but only display the productNumber where mainNumber=1.

How do I do that?

Thanks!

Upvotes: 0

Views: 518

Answers (2)

jods
jods

Reputation: 4591

Not totally sure I understood... In the result set you want only the first row but which value should be in the productNumber column?

If just about any value would do, you must not GROUP BY productNumber (which keeps the rows distinct) but aggregate it, e.g. with MIN or MAX.

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

If I understood the question correctly, this may help you:

SELECT salesID, historyID, productID, name, productNumber, total.quantity
FROM table1
JOIN (
     SELECT salesID, SUM(quantity) AS quantity FROM table1 GROUP BY salesID
     ) AS total
ON table1.salesID = total.salesID
WHERE mainNumber=1

Upvotes: 1

Related Questions