conversion mysql to postgresql

Question

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.

Joop Eggen · Accepted Answer · 2014-02-10 14:40:41Z

2

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.

edited Feb 10, 2014 at 14:40

answered Feb 10, 2014 at 11:55

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

knagode · Accepted Answer · 2014-02-10 11:47:51Z

1

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

answered Feb 10, 2014 at 11:47

knagode

5,9735 gold badges53 silver badges73 bronze badges

Collectives™ on Stack Overflow

conversion mysql to postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related