I have an original table like this,
timestamp_ID Country col1 col2 col3 2021-01-04 11:00:00 US red phone car 2021-01-04 11:00:00 US red sms car 2021-01-04 12:00:00 US red phone car 2021-01-07 11:00:00 UK red phone car 2021-01-08 11:00:00 US red phone airplane 2021-01-11 11:00:00 UK red sms car 2021-01-11 11:00:00 US green phone car 2021-01-12 11:00:00 US red phone car 2021-01-16 11:00:00 CA red sms car 2021-01-18 11:00:00 US blue phone airplane 2021-01-19 11:00:00 AU red phone car 2021-01-19 11:00:00 AU blue phone train
Can I group my data using GROUP BY in SQL to get the aggregate value by each week?
My expected output will be like,
Country col1 col2 col3 count_at_week1(2021-01-04~2021-01-10) count_at_week2 count_at_week3 ...
US red phone car 2 0 0
US red sms car 1 0 0
UK red phone car 1 0 0
US red phone airplane 1 0 0
UK red sms car 0 1 0
US green phone car 0 1 0
US red phone car 0 1 0
CA red sms car 0 1 0
US blue phone airplane 0 0 1
AU red phone car 0 0 1
AU blue phone train 0 0 1
Advertisement
Answer
This is a way of doing it, probably not the best way however. Personally, I feel like this list is not something you’d want to do in SSMS. But rather inside Power BI, Excel or some other software for visualizing data rather than retreiving. Do note that my example below is NOT dynamic in the sense of having more and more weeks. This specifically only works for the 3 weeks specified. Please refer to the documentation to see which date belongs to what week. This is from SSMS v18.7.1
Anyway, here goes:
CREATE TABLE #list (
timestamp_ID datetime,
country varchar(255),
col1 varchar(255),
col2 varchar(255),
col3 varchar(255),
)
insert into #list (timestamp_ID, country, col1, col2, col3)
Values ('2021-01-04 11:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-04 11:00:00', 'US', 'red', 'sms', 'car'),
('2021-01-04 12:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-07 11:00:00', 'UK', 'red', 'phone', 'car'),
('2021-01-08 11:00:00', 'US', 'red', 'phone', 'airplane'),
('2021-01-11 11:00:00', 'UK', 'red', 'sms', 'car'),
('2021-01-11 11:00:00', 'US', 'green', 'phone', 'car'),
('2021-01-12 11:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-16 11:00:00', 'CA', 'red', 'sms', 'car'),
('2021-01-18 11:00:00', 'US', 'blue', 'phone', 'airplane'),
('2021-01-19 11:00:00', 'AU', 'red', 'phone', 'car'),
('2021-01-19 11:00:00', 'AU', 'blue', 'phone', 'train ');
select *, DATEPART(Week,a.timestamp_ID) As 'week'
into #week
from #list a
select a.country, a.col1, a.col2, a.col3, count(*) as 'Amount', a.week
into #filter
from #week a
group by a.country, a.col1, a.col2, a.col3, a.week
select a.country,
a.col1,
a.col2,
a.col3,
case when b.Amount is null then 0 else b.Amount end as 'Week 2',
case when c.Amount is null then 0 else c.Amount end as 'Week 3',
case when d.Amount is null then 0 else d.Amount end as 'Week 4'
from #week a
left join #filter b
on a.country=b.country and a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and b.week = 2
left join #filter c
on a.country=c.country and a.col1=c.col1 and a.col2=c.col2 and a.col3=c.col3 and c.week = 3
left join #filter d
on a.country=d.country and a.col1=d.col1 and a.col2=d.col2 and a.col3=d.col3 and d.week = 4
group by a.country, a.col1, a.col2, a.col3, b.Amount, c.Amount, d.Amount
drop table #list
drop table #filter
drop table #week