How to get sum of values in a column using OVER() function?

Question

So I have a table that has stores information per student, their date of birth and the start date of the school. The output needed is % of students grouped by their age

The query in Biqquery is written as follows,

Select ROUND(100*(Number_of_Students/Total),2) as Percent_Students,Age FROM

(SELECT count(participantname) as Number_of_Students, 
SUM(count(participantname)) OVER() AS Total,
CAST(round(date_diff(startdate, participantdob,day) / 365.25)  AS INT64) as Age 
FROM `ParticipantEnrolment` 

group by Age
order by Age)

The output of the inner query is

Row Number_of_Students  Total   Age 
1   1                   826     12
2   259                 826     13
3   358                 826     14
4   145                 826     15
5   47                  826     16
6   16                  826     17

where Total = 826 is the sum of Number_of_Students

When trying this same query in WorkBench - I am getting a syntax error in the inner query because of OVER() function. The datediff function change is working as intended in mysql. The revised query is below

SELECT count(participantname) as Number_of_Students,
SUM(count(participantname)) OVER() AS TOTAL,
ROUND(timestampdiff(DAY,participantdob,startdate)/365.25) as AGE

FROM `ParticipantEnrolment`
group by AGE
order by AGE

If i remove the OVER() statement, the query works properly. Unable to figure out what the syntax issue is in this case! Any help would be appreciated

Which version of MySQL are you running? – GMB Jul 28 '20 at 14:18 — GMB, Jul 28 '20 at 14:18

score 0 · Answer 1 · answered Jul 28 '20 at 14:20

Likely, you are running MySQL 5.7 or earlier, where window functions are not supported. You can work around this with a subquery:

select 
    count(*) as number_of_students,
    (select count(*) from participantenrolment) as total,
    round(timestampdiff(day, participantdob, startdate) / 365.25) as age
from participantenrolment
group by age
order by age

Note that I replaced count(participantname) with count(*): assuming that participantname is not nullable, both expressions are functionally equivalent, and count(*) is more efficient. You can change that back if participantname is a nullable column.

Thank you. This worked. Really appreciate the help! – Anirudh Prasadh Jul 28 '20 at 14:30 — Anirudh Prasadh, Jul 28 '20 at 14:30

How to get sum of values in a column using OVER() function?

1 Answers1