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
CodePudding user response：
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
CodePudding user response：
create table damith.temp_test_01
(timestamp_ID TIMESTAMP,
Country varchar2(5),
col1 varchar2(8),
col2 varchar2(8),
col3 varchar2(10)
);
insert into damith.temp_test_01 values ( TIMESTAMP '2021-01-04 11:00:00' , 'US' ,'red','phone','car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-04 11:00:00' , 'US','red','sms','car' );
insert into damith.temp_test_01 values ( TIMESTAMP '2021-01-04 12:00:00' , 'US','red', 'phone','car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-07 11:00:00' , 'UK','red', 'phone','car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-08 11:00:00' , 'US','red', 'phone', 'airplane' ) ;
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-11 11:00:00' , 'UK','red', 'sms', 'car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-11 11:00:00' , 'US','green', 'phone' , 'car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-12 11:00:00' , 'US','red', 'phone' , 'car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-16 11:00:00' , 'CA','red', 'sms' , 'car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-18 11:00:00' , 'US','blue', 'phone' , 'airplane' ) ;
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-19 11:00:00' , 'AU', 'red', 'phone' , 'car' );
insert into damith.temp_test_01 values (TIMESTAMP '2021-01-19 11:00:00' , 'AU', 'blue', 'phone' , 'train' );
commit;
SELECT a.*, nvl(b.number_of_sale,0) as count_at_week_01,nvl(c.number_of_sale,0) as count_at_week_02,nvl(d.number_of_sale,0)
as count_at_week_03 FROM
(SELECT distinct COUNTRY, COL1, COL2, COL3 FROM damith.temp_test_01)a left outer join
(SELECT COUNTRY, COL1, COL2, COL3, count(*) as number_of_sale FROM damith.temp_test_01 where
trunc(timestamp_ID) >='04-JAN-21' and trunc(timestamp_ID) <='10-JAN-21'
group by COUNTRY, COL1, COL2, COL3
)b on a.COUNTRY= b.COUNTRY and a.COL1= b.COL1 and a.COL2=b.COL2 and a.COL3=b.COL3
left outer join
(SELECT COUNTRY, COL1, COL2, COL3, count(*) as number_of_sale FROM damith.temp_test_01 where
trunc(timestamp_ID) >='10-JAN-21' and trunc(timestamp_ID) <='17-JAN-21'
group by COUNTRY, COL1, COL2, COL3
)c on a.COUNTRY= c.COUNTRY and a.COL1= c.COL1 and a.COL2=c.COL2 and a.COL3=c.COL3
left outer join
(SELECT COUNTRY, COL1, COL2, COL3, count(*) as number_of_sale FROM damith.temp_test_01 where
trunc(timestamp_ID) >='17-JAN-21'
group by COUNTRY, COL1, COL2, COL3
)d on a.COUNTRY= d.COUNTRY and a.COL1= d.COL1 and a.COL2=d.COL2 and a.COL3=d.COL3
;