Multiple times Grouping with where in SQL Server

Question

I have a source table like this

ProductName SaleReceipt SaleCode
--------------------------------
F-Apple     1001        1
F-Orange    1002        2
G-Rice      1003        3
G-Barile    1005        4
G-Oats      1006        1
V-Carrot    1007        4
V-Cabbage   1008        3
V-Potato    1009        1
V-Tomato    1010        1
Chocolate   1011        4
Cookies     1012        1
Cakes       1013        2

I need to create a report like this

                30 Day delay    60 Day Delay    90 day delay    120 day delay
Fruits          1               1               0               0
Grains          1               0               1               1
Vegetables      2               0               1               1 
Other category  1               1               0               1

The conditions to create the report are:

• All ProductName start with F is grouped as fruits
• All ProductName start with G is grouped as Grains
• All ProductName start with V is grouped as Vegetables

• Other product name go in the other category

• 30 day delay: count of (SaleReceipt) with a SaleCode=1

• 60 day delay: count of (SaleReceipt) with a SaleCode=2
• 90 day delay: count of (SaleReceipt) with a SaleCode=3

• 120 day delay: count of (SaleReceipt) with a SaleCode>=4

  I could not find how to do grouping two times. I am using SQL Server 2014.

Answer 1

You can use case in a group by. Or use a lookup table:

select coalesce(c.name, 'Other category'),
       sum(case when salescode = 1 then 1 else 0 end) as salecode_1,
       sum(case when salescode = 2 then 1 else 0 end) as salecode_2,
       sum(case when salescode = 3 then 1 else 0 end) as salecode_3,
       sum(case when salescode = 4 then 1 else 0 end) as salecode_4
from t left join
     (values ('F-%', 'Fruit'), ('G-%' , 'Grain'), ('V-%', 'Vegetable')
     ) c(pattern, name)
     on t.productname like c.pattern
group by coalesce(c.name, 'Other category');