Converting a MySQL GROUP BY to Postgres

Question

I'm working out a query that I've ran successfully in MySQL for a while, but in Postgres it's not working with the ole -

ERROR:  column "orders.created_at" must appear in the GROUP BY clause 
or be used in an aggregate function

Here's the query:

SELECT SUM(total) AS total, to_char(created_at, 'YYYY/MM/DD') AS order_date 
FROM orders 
WHERE created_at >= (NOW() - INTERVAL '2 DAYS') 
GROUP BY to_char(created_at, 'DD') 
ORDER BY created_at ASC;

It's just supposed to return something like this:

  total  | order_date 
---------+------------
 1099.90 | 2013/01/15
  650.00 | 2013/01/16
 4399.00 | 2013/01/17

The main thing is I want the sum grouped by each individual day of the month.

Anyone have ideas?

UPDATE:

The reason I'm grouping by day is because the graph will be labeled with each day of the month, and the total sales for each.

1st - $3400.00
2nd - $2237.00
3rd - $1489.00

etc.

If you're only looking at the last two days, why can't you group by the same thing you're using in your select list, namely ymd not just day? — Scott Marlowe
– Scott Marlowe, Commented Jan 18, 2013 at 6:40

Ilion · Accepted Answer · 2013-01-18 03:44:52Z

2

I'm not sure why you're doing a conversion there. I think the better thing to do would be this:

SELECT 
  SUM(total) AS total, 
  created_at::date AS order_date 
FROM 
  orders 
WHERE 
  created_at >= (NOW() - INTERVAL '2 DAYS') 
GROUP BY 
  created_at::date 
ORDER BY 
  created_at::date ASC;

I would recommend this query and then format the daily labels in your graph through the graph settings to ensure you do not have any weird issues of the same day in different months getting grouped. However, to get what you display in your edit you can do this:

SELECT 
  SUM(total) AS total, 
  to_char(created_at, 'DDth') AS order_date 
FROM 
  orders 
WHERE 
  created_at >= (NOW() - INTERVAL '2 DAYS') 
GROUP BY 
  to_char(created_at, 'DDth') 
ORDER BY 
  to_char(created_at, 'DDth') ASC;

edited Jan 18, 2013 at 3:44

answered Jan 18, 2013 at 2:49

Ilion

6,8923 gold badges28 silver badges47 bronze badges

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

5 Comments

joshnabbott Over a year ago

I was only doing it because I didn't know about this way. I agree, it's better. Only thing is that the GROUP BY needs to be DAY, not date. Also the order by clause caused the same ERROR: column "orders.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Ilion Over a year ago

Awesome. :) please remember to mark the answer which you found most useful as accepted by clicking the white check mark next to it. You need to wait 15 minutes or so after asking your question before you can accept an answer.

joshnabbott Over a year ago

Your answer is very useful, but since I still haven't had a reply that groups by the day of the month (and not the date), I'm waiting for one that shows me how to do that.

Ilion Over a year ago

Sorry I was on my phone and only saw half your response. Your example shows information grouped by the date. You can't display a full date and only group by the day value (which would end up grouping say Jan 1st, Feb 1st, etc), at least not in any meaningful way. What are you trying to accomplish when you say you want it grouped b the day only?

joshnabbott Over a year ago

This is it, my friend! Thank you. Postgres grouping is not near as forgiving as MySQL's :)

MatthewJ · Accepted Answer · 2013-01-18 02:44:59Z

2

Here is the sql you need in order to run this. The group by and order by need to contain the same expression.

SELECT SUM(total) AS total, 
to_char(created_at, 'YYYY/MM/DD') AS order_date 
FROM orders 
WHERE created_at >= (NOW() - INTERVAL '2 DAYS') 
GROUP BY to_char(created_at, 'YYYY/MM/DD')
order by  to_char(created_at, 'YYYY/MM/DD')

http://sqlfiddle.com/#!12/52d99/2

Hope this helps, Matt

answered Jan 18, 2013 at 2:44

MatthewJ

3,1972 gold badges29 silver badges34 bronze badges

3 Comments

joshnabbott Over a year ago

Thanks, but the same thing... GROUP BY needs to be DAY only. I'm outputting a graph with the total sales per day.

joshnabbott Over a year ago

Isn't this still being grouped by the date "GROUP BY to_char(created_at, 'YYYY/MM/DD')"?

Ilion Over a year ago

@joshnabbott Both answers do give the total sales per day. Is your problem simply the labeling of your graph perhaps?

Collectives™ on Stack Overflow

Converting a MySQL GROUP BY to Postgres

UPDATE:

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE:

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related