Matt Ellis Matt Ellis - 20 days ago 6
SQL Question

SQL sum quantity depending on field name

How would I go about adding the quantity of a column to each other depending on another columns name?

For example I have 3 columns:

NAME: StockCode: Qty:
Blackbirds TO12 2
Blackbirds TO14 4
Lions TO55 54
Tigers TO41 2
Lions TO41 4


How would I add the qty together for both the blackbirds and lions, so that there is only one row, so the result would be as follows:

NAME: StockCode: Qty:
Blackbirds TO12 6
Lions TO41 58
Tigers TO32 2

Answer

You can try this:

SELECT NAME, SUM(QTY) as qty FROM [YourTable]
GROUP BY NAME

PS: Since the StockCode are different for same NAME, They cannot be included in the GROUP BY. Incase, StockCode doesn't matter and you need just the SUM based on NAME, then you could use the below solution.

SELECT NAME,StockCode, SUM(QTY) OVER(PARTITION BY NAME) as qty FROM [YourTable]