How can I sum a row's value in my table based on a specific type?-CodePudding

My table looks like this. Its a table with an inventory of clothes.

Basically, an user can enter a type of clothe and a quantity. When he did it, it add a new value in the table with the date of the input.

The type 2 is for shoes and the type 3 for shirts

What I'm trying to do is to sum the quantity based on the type like this :

So I tried this :

SELECT name, type, sum(quantity)
from Clothes
where type="2"
group by name

But it didn't work, it sums all the type of clothes. How can I do ?

CodePudding user response：

Use case expressions to do conditional aggregation:

SELECT name,
       SUM(case when type = 3 then quantity else 0 end) shirts,
       SUM(case when type = 2 then quantity else 0 end) shoes
from Clothes
group by name

CodePudding user response：

You should group using the type too. Doing this you'll get a table with 3 columns: 1st one with the name, secondo col with the type and the third with the quantity

SELECT name, type, sum(quantity)
from Clothes
group by name,type

Then you should format as you wish the data

If otherwise you want to get the exact result with a query you should dig more deep and maybe using some 'Case' inside the sum function and put a zero if is not of the selected type:

select name, 
sum(case when type = 3 then quantity else 0 end) as Shirts,
sum(case when type = 2 then quantity else 0 end) as Shoes
from Clothes
group by name;

result:

CodePudding user response：

A solution using a PIVOT table will achieve the same result with multi-column aggregation of quantities corresponding to the type column:

SELECT [ProductName], [2] As Shoes, [3] As Clothes
FROM 
(SELECT [ProductName], [ProductType], [Quantity] FROM [Inventory_Table])
AS DataSource
PIVOT 
(SUM([Quantity]) FOR [ProductType] IN ([2], [3])) AS pvt_table

Note: For the above to work in SQL Server T-SQL I had to replace the [Name] and [Type] columns with other columns names.