英文:
Why is this query returning each student - month pairing individually?
问题
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 DESC;
英文:
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
答案1
得分: 1
你正在按RegistrationDate进行分组，它具有1秒的粒度。只要每秒不超过一次记录，你将看到1s。要修复这个问题，可以在GROUP BY中使用与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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论