I have the following data set:

I would like to group data first by advisor and whin this group by weekday. So I have a pivot style table showing what is the total value of transactions made by each advisor, and then showing the breakdown by weekday. I just started learning, and this is one of the exercises I need to solve. The solution that was suggested is to go for a GROUP BY advisor, weekday, but this does not break data in the way I want it.

CodePudding user response：

try using a pivot

declare @tmp as table(Advisor varchar(20),MyWeekday varchar(5), TransactionValue money)

insert into @tmp
values('Oscar', 'Mon',  5.30),
('Oscar', 'Tue',    10.20),
('Bill',     'Tue', 6.30),
('Bill',     'Wed', 9.60),
('Emily', 'Wed',    4.40),
('Emily', 'Wed',    12.02),
('Emily', 'Thur',   8.60);

select
Advisor,IsNull([Mon],0) Mon,IsNull([Tue],0)Tue,IsNull([Wed],0) Wed,IsNull([Thur],0)Thur,IsNull([Fri],0)Fri,IsNull([Sat],0)Sat,IsNull([Sun],0)Sun
from 
(
select Advisor,MyWeekday,TransactionValue from @tmp)p
pivot
(
sum(TransactionValue)
for MyWeekday in([Mon],[Tue],[Wed],[Thur],[Fri],[Sat],[Sun])
) as pvt 

CodePudding user response：

Oh... I think what you need is not "group by"... It can only make aggregation, can't provide both aggregation and details in presentation. Here what you need is window function in SQL language -- Here's a sample on sybase IQ for you --

(DBA)> select * from adv_tran;
advisor              weekday                  tran_value
--------------------------------------------------------
Oscar                Mon                             5.3
Oscar                Tue                            10.2
Bill                 Tue                             6.3
Bill                 Wed                             9.6
Emily                Wed                             4.4
Emily                Wed                           12.02
Emily                Thur                            8.6

(7 rows)
(DBA)> select Advisor,sum(tran_value) over(partition by advisor,weekday) as day_total,weekday,tran_value from adv_tran order by 1,3;
Advisor                             day_total weekday                  tran_value
---------------------------------------------------------------------------------
Bill                        6.300000190734863 Tue                             6.3
Bill                        9.600000381469727 Wed                             9.6
Emily                       8.600000381469727 Thur                            8.6
Emily                      16.420000553131104 Wed                           12.02
Emily                      16.420000553131104 Wed                             4.4
Oscar                       5.300000190734863 Mon                             5.3
Oscar                      10.199999809265137 Tue                            10.2

(7 rows)

(DBA)> select Advisor,sum(tran_value) over(partition by advisor) as total,weekday,tran_value from adv_tran order by 1,3;
Advisor                                 total weekday                  tran_value
---------------------------------------------------------------------------------
Bill                        15.90000057220459 Tue                             6.3
Bill                        15.90000057220459 Wed                             9.6
Emily                       25.02000093460083 Thur                            8.6
Emily                       25.02000093460083 Wed                           12.02
Emily                       25.02000093460083 Wed                             4.4
Oscar                                    15.5 Mon                             5.3
Oscar                                    15.5 Tue                            10.2

(7 rows)