Why is this query returning each student – month pairing individually?

February 24, 2023

SELECT To_Char(registrationdate, 'Month') AS "REGDATE", COUNT(*) AS "NUMSTUDENTS"
FROM registration r
INNER JOIN students s
ON r.studentid = s.studentid
GROUP BY registrationdate
ORDER BY NUMSTUDENTS;

Results are coming back like this:

May         1
April       1
April       1
May         1
April       1
May         1
April       1
May         1

And the expected results are

June                                             213
March                                            258
October                                          291
January                                          325
July                                             328
September                                        333
November                                         359
etc.

I am just trying to get the results to be total students per month, I don’t know what I’m missing

>Solution :

You are grouping by RegistrationDate, which has a 1-second granularity. As long as no more than one entry occurs per second, you are going to see 1s. To fix, put the same function on it in the GROUP BY that you did in the SELECT:

SELECT To_Char(registrationdate, 'Month') AS "REGDATE", COUNT(*) AS "NUMSTUDENTS"
FROM registration r
INNER JOIN students s
ON r.studentid = s.studentid
GROUP BY To_Char(registrationdate, 'Month')
ORDER BY NUMSTUDENTS;