Sum of a higher level aggregation

Question

I have a table defined in the below code, I expect to have a sum of all values as ALLDays, in this case for Ajan 36 and for Sam 21. How can I modify the below query to get that, I'm not allowed to use windows function.I can achieve the requirment by having another query grouped by student and join both but is there any way to modify the below query to cater the requirement. any help is much appreciated.

DECLARE @Table 
TABLE(Student varchar(50),
subject varchar(50)
,days int)

Insert into @Table
values('Ajan','English',8),('Ajan','Math',9),('Ajan','Science',7),('Ajan','English',5),('Ajan','Math',4),('Ajan','Science',3),
('Sam','English',7),('Sam','Math',6),('Sam','Science',8)


select student,subject,sum(days) as SubjectDays,'' as AllDays from @Table
group by student,subject

I count 36 days for Ajan, not 34. – Tim Biegeleisen Dec 19 '18 at 08:14 — Tim Biegeleisen, Dec 19 '18 at 08:14

Tim Biegeleisen · Accepted Answer · 2018-12-19T08:18:24.530

2

If you can't use window functions, then one alternative would be to use two separate subqueries for the student and subject level sums of days.

select t1.student, t1.subject, t1.SubjectDays, t2.AllDays
from
(
    select student, subject, sum(days) as SubjectDays
    from @Table
    group by student, subject
) t1
inner join
(
    select student, sum(days) as AllDays
    from @Table
    group by student
) t2
    on t1.student = t2.student;

edited Dec 19 '18 at 08:18

answered Dec 19 '18 at 08:13

Tim Biegeleisen

502,043
27
286
360

yes but I need both of them in the same query without any join, is it possible ? – Ajan Balakumaran Dec 19 '18 at 08:14

WernerW · Answer 2 · 2018-12-19T08:58:07.283

Maybe you can use a CTE to contain the total sum and join against that:

DECLARE @Table 
TABLE(Student varchar(50),
subject varchar(50)
,days int)

Insert into @Table
values('Ajan','English',8),('Ajan','Math',9),('Ajan','Science',7),('Ajan','English',5),('Ajan','Math',4),('Ajan','Science',3),
('Sam','English',7),('Sam','Math',6),('Sam','Science',8);


WITH MainSummary (Student,Alldays) AS
(
  SELECT Student,SUM([days]) as AllDays
  FROM @Table
  GROUP BY Student
)
SELECT 
 T.Student
 ,T.[subject]
 ,SUM([Days]) AS SubjectDays
 ,MAX(MS.AllDays) AS AllDays
FROM @Table AS T
LEFT JOIN MainSummary AS MS ON MS.Student = T.Student
GROUP BY T.Student,T.[Subject]

score 0 · Answer 3 · answered Dec 19 '18 at 09:33

0

After more searching I identified a way to cater the requirement without any joins,

DECLARE @Table 
TABLE(Student varchar(50),
subject varchar(50)
,days int)

Insert into @Table
values('Ajan','English',8),('Ajan','Math',9),('Ajan','Science',7),('Ajan','English',5),('Ajan','Math',4),('Ajan','Science',3),
('Sam','English',7),('Sam','Math',6),('Sam','Science',8);

SELECT student,subject,sum(days) as SubjectDays,
    (SELECT sum(days) from  @Table b where b.Student=a.Student ) as Alldays 
    FROM @Table a
    group by student,subject

answered Dec 19 '18 at 09:33

Ajan Balakumaran

1,639
1
8
16

1

Using subselects is usually less efficient than using a join, when testing and running the query against 10 rows you will not be impacted by it, but try generating a million rows in your table and rerun it. Just a friendly heads up https://stackoverflow.com/questions/2577174/join-vs-sub-query – WernerW Dec 19 '18 at 10:35

Sum of a higher level aggregation

3 Answers3