GROUP BY with a Formatted Date and Left Join

Question

I am having trouble making formatted dates group properly, I am thinking it might have to do with my left join, but I am not sure the path to take. Heres my query and results. For simplicity below I have not included the additional columns but note that utilizing distinct for the select, I don't believe is an option as it throws off my calculations.

When I use this:

SELECT DATE_FORMAT(posts.post_date, "%Y%m%d") AS 'Date', COUNT(*) AS 'Orders'
FROM wp_posts AS posts
LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
WHERE meta.meta_key = '_order_total'
AND posts.post_type = 'shop_order'
AND posts.post_status = 'wc-completed'

I get this:

When I add the Group By, the results just drop down to a single line:

SELECT DATE_FORMAT(posts.post_date, "%Y%m%d") AS 'Date'
FROM wp_posts AS posts
LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
WHERE meta.meta_key = '_order_total'
AND posts.post_type = 'shop_order'
AND posts.post_status = 'wc-completed'
GROUP BY 'Date'

I get this:

20170204

My desired output is:

You are currently grouping by the string literal `'Date'`, which won't work. Just repeat what you are selecting already, i.e. `DATE_FORMAT(...)`. — Tim Biegeleisen, May 15 '17 at 03:04
You can group by the alias, but you should not quote the alias to begin with... `DATE_FORMAT(...) AS Date` (unquoted or backtick quoted) and then you can use `GROUP BY Date` (also unquoted)http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql — Michael Berkowski, May 15 '17 at 11:24

GROUP BY with a Formatted Date and Left Join

0 Answers0