Get distinct values and sum their respective quantities-CodePudding

I have a problem. I have a result query with order numbers item numbers and different quantities for each item. I want to distinct all item numbers and count all quantities for each specific item number.

Here is an example table (Query output):

|  OrderNo  |  ItemNo  |  Qty  |
--------------------------------
|  XY123    |  3000    |  4    |
|  XY123    |  2000    |  2    |
|  ZZ999    |  3000    |  6    |
|  ZZ999    |  1000    |  3    |
|  PP333    |  1000    |  5    |

The distinct values for all sold items with their item numbers would be:

1000 -> Count/Sum the Qty
2000 -> Count/Sum the Qty
3000 -> Count/Sum the Qty

Result:

|  ItemNo  |  QtyTotal  |
-------------------------
|  1000    |   8        |
|  2000    |   2        |
|  3000    |   10       |

My problem is, when I DISTINCT the ItemNo, i dont know how to SUM their corresponding quantities before. I need some advice please.

CodePudding user response：

You can use group by:

select ItemNo, sum(Qty) as QtyTotal
from QueryOutput q
group by ItemNo;

You can replace QueryOutput with a query that produces your example table.

Fiddle