Help with Multiple group by SQL Server Query

Question

I'm having some troubles to retrieve data from 2 tables with using multi "group by".

Below is 2 tables for example and the result I would like to have from my query.

PrdID    Name   KG
------------------
1    Hals       10
2    Hals       3
3    Kugel      4
4    Kugel      10
5    Hals       12
6    Kugel      11
7    Hals       12
8    Hals       14
9    Hals       15
10   Kugel      16
11   Hals       8
12   Hals       15
13   Kugel      7
14   Kugel      8
15   Kugel      9

Materials

PrdId    MatID    MatSize
-------------------------
1    a    300/600
2    b    350/500
5    c    400/650
3    b    350/500
4    c    400/650
6    d    450/650
9    b    350/500
10   d    450/650
13   d    450/650
11   c    400/650
12   b    350/500
14   c    400/650
15   d    450/650
7    a    300/600
8    b    350/500

Result table

Name     MatSize    Kg
----------------------
Hals    300/600    22
Hals    350/500    47
Hals    400/650    20
Kugel   350/500    4
Kugel   400/650    18
Kugel   450/650    43

In summary I want to know amount of Kg used per each product on each material...

Answer 1

I think this is what you want

select P.Name, M.MatSize, sum(P.KG) as Kg
from Products P
join Material M
on P.PrdId = M.PrdId
Group By P.Name, M.MatId, M.MatSize

(Edited to return just the wnted columns)

As a side, can I suggest you normalise your tables some more to something like Products[PrdId, Name, Kg] Materials[MatId, MatSize] ProductMaterials[PrdId, MatId]

Then the query would become

select P.Name, M.MatSize, sum(P.KG) as KG
from Products P
join ProductMaterial PM
on PM.PrdId = P.PrdId
join Materials M
on M.MatId = PM.MatId
group by P.Name, M.MatId, P.MatSize

The benefit of this is you are only storing the MatSize once for each material type.