Why is my group by not grouping my values as expected?-CodePudding

My project is a clothes factory and I have 3 tables.

Person : A table that contains people's name
Category : Contains each category of clothes in the factory (sockets, shoes, etc.)
Quantity : The quantity of clothes for each person

SELECT  Name,
    sum(case when category = 'shoes' then quantity else 0 end) as 'Quantity_shoes',
    sum(case when category = 'shirts' then quantity else 0 end) as 'Quantity_shirts',
    sum(case when category = 'sockets' then quantity else 0 end) as 'Quantity_shirts',
    sum(case when category = 'hats' then quantity else 0 end) as 'Quantity_hats'
FROM person p
    join inventory i 
     on i.person_id = p.id
    join category c
      c.id = i.category_id
WHERE p = 'Paul'
GROUP BY name,
     category

And I'm trying to display something like this :

Name	Quantity_shoes	Quantity_shirts	Quantity_sockets	Quantity_hats
Paul	8	25	38	0

But my result isn't that I expected... I got this :

Name	Quantity_shoes	Quantity_shirts	Quantity_sockets
Paul	8	0	0
Paul	0	0	0
Paul	0	25	0
Paul	0	0	38

It seems that I have one row for each category. So I tried to groupbyquantity but it doesn't sum my quantity and I have more rows.

What I am doing wrong?

CodePudding user response：

As suggested in the above try this (I am guessing the column in person with the name is p.name in which case you may not even need the group by name):

SELECT
    Name,
    sum(case when category = 'shoes' then quantity else 0 end) as 'Quantity_shoes',
    sum(case when category = 'shirts' then quantity else 0 end) as 'Quantity_shirts',
    sum(case when category = 'sockets' then quantity else 0 end) as 'Quantity_shirts',
    sum(case when category = 'hats' then quantity else 0 end) as 'Quantity_hats'
FROM
    person p
    join inventory i on i.person_id = p.id
    join category c.id = i.category_id
WHERE
    p.name = 'Paul'