w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
SQL - Dividing by Sum by Group

Your problem is a common problem -- trying to summarize along two dimensions at the same time without using a subquery. You want to do this query with two aggregation subqueries. Something like this:

SELECT *
FROM (SELECT sum(case when cd.DATE_VALUE >= s.ENTRYDATE and
cd.DATE_VALUE <= current_timestamp 
                     THEN cd.INSESSION
                     ELSE 0
                 END), s. STUDENT_NUMBER
      FROM CALENDAR_DAY cd CROSS JOIN
           STUDENTS s
      WHERE cd.SCHOOLID = 405
      GROUP BY s.STUDENT_NUMBER
     ) sc JOIN
     (SELECT s.STUDENT_NUMBER, s.LASTFIRST,
             SUM(CASE WHEN a.ATTENDANCE_CODEID = 2 THEN 1 ELSE 0 END),
             SUM(CASE WHEN a.ATTENDANCE_CODEID = 4 THEN 1 ELSE 0 END),
             SUM(CASE WHEN a.ATTENDANCE_CODEID = 3 THEN 1 ELSE 0 END),
             SUM(CASE WHEN a.ATTENDANCE_CODEID = 51 THEN 1 ELSE 0 END)
      FROM ATTENDANCE a INNER join
           STUDENTS s
           ON a.STUDENTID = s.ID
      WHERE a.att_date between '%param1%'  and  '%param2%'
      GROUP BY s.STUDENT_NUMBER, s.LASTFIRST
     ) sa
     on sc.STUDENT_NUMBER = sa.STUDENT_NUMBER;




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.