Translating MySQL query to Postgres

Question

I have this MySQL query which works perfectly for what i need it to do. However, I need to translate this across to a postgres installation, and seem to be having some trouble. The query is as follows:

SELECT r.studno, r.sdate,r.subject,
  sum(exam1) as exam1,sum(exam2) as exam2,sum(asgn1) as asgn1,sum(asgn2) as asgn2,
  sum(proj1) as proj1,sum(proj2) as proj2,sum(pract1) as pract1, sum(pract2) as pract2,
  r.overallmark, r.result, r.credits, r.corlevel, r.nceaaward, r.gpa, r.overallresult 
FROM exam_results r WHERE r.studno = :studno AND r.sdate = :sdate GROUP BY r.studno, r.subject

I've tried running this in postgres but get the following error:

2014-06-23 11:56:54 IST ERROR: column "r.sdate" must appear in the GROUP BY clause or be used in an aggregate function at character 8

How can i go about resolving this?

Read this : http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql ;) — kmas, Jun 23 '14 at 12:14

score 1 · Accepted Answer · answered Jun 23 '14 at 12:14

Your query is using a MySQL extension that allows columns in the select not to be in the group by. You can fix the query by wrapping all such columns in an aggregation function such as max():

SELECT r.studno, r.sdate, r.subject,
       sum(exam1) as exam1,sum(exam2) as exam2,sum(asgn1) as asgn1,sum(asgn2) as asgn2,
       sum(proj1) as proj1,sum(proj2) as proj2,sum(pract1) as pract1, sum(pract2) as pract2,
       max(r.overallmark), max(r.result), max(r.credits),
       max(r.corlevel), max(r.nceaaward), max(r.gpa), max(r.overallresult) 
FROM exam_results r
WHERE r.studno = :studno AND r.sdate = :sdate
GROUP BY r.studno, r.sdate, r.subject;

Translating MySQL query to Postgres

1 Answers1