CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Zy0n
Zy0n

Reputation: 785

Translating MySQL query to Postgres

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?

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 1

Related Questions