-- drop table mytransactions;
create table mytransactions(
country varchar(30),
totalcount int,
numericmonth int,
chardate char(20),
totalamount money
);
insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 36, 7, 'Jul-12', 699.96
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 44, 8, 'Aug-12', 1368.71
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 52, 9, 'Sep-12', 1161.33
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 50, 10, 'Oct-12', 1099.84
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 38, 11, 'Nov-12', 1078.94
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Australia', 63, 12, 'Dec-12', 1668.23
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
('Austria', 11, 7, 'Jul-12', 257.82) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
('Austria', 5, 8, 'Aug-12', 126.55) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
('Austria', 7, 9, 'Sep-12', 92.11) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Austria', 12, 10, 'Oct-12', 103.56
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
(
'Austria', 21, 11, 'Nov-12', 377.68
) insert into mytransactions(
country, totalcount, numericmonth,
chardate, totalamount
)
values
('Austria', 3, 12, 'Dec-12', 14.35)
;
select *
from mytransactions
;
select chardate, Australia, Austria
from mytransactions
pivot ( sum(totalcount) for country in ( [Australia], [Austria] ) ) as piv
;
select numericmonth, chardate
, sum(cnt.Australia) as cnt_Australia
, sum(cnt.Austria) as cnt_Austria
from mytransactions
pivot ( sum(totalcount ) for country in ( [Australia], [Austria] ) ) as cnt
--pivot ( sum(totalamount ) for tot.country in ( [Australia], [Austria] ) ) as tot
group by numericmonth, chardate
order by numericmonth
;
select numericmonth, chardate
, sum(cnt.Australia) as cnt_Australia
, sum(cnt.Austria) as cnt_Austria
from mytransactions
--pivot ( sum(totalcount ) for country in ( [Australia], [Austria] ) ) as cnt
pivot ( sum(totalamount ) for country in ( [Australia], [Austria] ) ) as tot
group by numericmonth, chardate
order by numericmonth
;
Telegram: DB developers channel