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 : stackoverflow.com/questions/1769361/… ;)

kmas
– kmas

2014-06-23 12:14:00 +00:00
Commented Jun 23, 2014 at 12:14 — kmas
– kmas, Commented Jun 23, 2014 at 12:14

Gordon Linoff · Accepted Answer · 2014-06-23 12:14:37Z

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;

Collectives™ on Stack Overflow

Translating MySQL query to Postgres

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related