CODEWITHSUNDEEP
sqlreturn-valuematchingcounting
user2285167
user2285167

Reputation: 55

Microsoft SQL - Counting total of matching values in other table

I have a SQL data scructure like this. 

Table 1
http://pbrd.co/1x6TAl3

Table 2
http://pbrd.co/1x6TIRw

I'm trying to count the number of times each item_num has been sold based on the item_qty value in the second table. Each item_num can appear multiple times in the second table. I need a way to add the total item_qty for each associated item_num and output it to show how many times an item has been sold.

The correct output ordering by total quantity sold in descending order should look like this. 

item_num: 4 7 6 
qty_sold: 11 5 4

Upvotes: 1

Views: 87

Answers (2)

Behnam
Behnam

Reputation: 1063

SELECT  A.Item_num , A.Item_name , sum(B.Item_Qty) from Table1  as A inner join Table2 as B
on A.Item_num=B.Item_num
group by A.Item_num , A.Item_name

result:

item_num    item_name     Item_qty
   1            A             1
   2            B             1
   4            D             11
   6            F             4
   7            G             5

Upvotes: 0

Andrei Hirsu
Andrei Hirsu

Reputation: 696

Try this:

SELECT
   a.item_num
   , SUM(b.item_qty) as "qty_sold"
FROM
   Table1 a
LEFT JOIN
   Table2 b
ON a.item_num = b.item_num
GROUP BY
   a.item_num
ORDER BY
   qty_sold DESC

Upvotes: 1

Related Questions