CODEWITHSUNDEEP
mysqlpostgresql
Marko Jurinčič
Marko Jurinčič

Reputation: 302

conversion mysql to postgresql

I have a working mysql query, but I can not get it work with postgres. This is the query (I already changed date format to to_char

SELECT country as grouper, date(users.created_at) as date, 
       to_char(users.created_at, '%Y-%m') as date_group, 
       count(id) as total_count 
  FROM "users" 
 WHERE (users.created_at >= '2011-12-01') 
   AND (users.created_at <= '2014-02-11') 
 GROUP BY grouper, date_group 
 ORDER BY date ASC

I am getting the error:

PG::Error: ERROR:  column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT country as grouper, date(users.created_at) as date, t...

Thank for your help.

Upvotes: 1

Views: 115

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109547

SELECT country as grouper, date(MIN(users.created_at)) as date, 
       to_char(MIN(users.created_at), '%Y-%m') as date_group, 
       count(id) as total_count 
  FROM "users" 
HAVING (users.created_at >= '2011-12-01') 
   AND (users.created_at <= '2014-02-11') 
 GROUP BY grouper, date_group 
 ORDER BY date ASC

MySQL is not very strict. In standard conform SQL all column values have to use an aggrate function (SUM, COUNT, MAX, MIN) on non-grouping fields - when using GROUP BY.

Honestly said, I am not entirely sure about data_group in the GROUP BY; can it be dropped? Also note that I have switched WHERE with a HAVING.

Upvotes: 2

knagode
knagode

Reputation: 6125

You should use every selected column in GROUP BY section. 

SELECT country as grouper, to_char(created_at, '%Y-%u') as date_group, count(id) as total_count
FROM "users" 
WHERE created_at >= '2013-10-01' 
AND created_at <= '2014-02-11' 
GROUP BY grouper, date_group 
ORDER BY date_group ASC

Upvotes: 1

Related Questions