MySQL = Grouping Sales Count by Months, then Displaying months as Names (Jan, Feb...DEC) not Numerical Value (1,2,3-12)

Question

This seems like a reasonably simple problem but I cannot figure out how to change a numerical value of a month into the months actual name after the sales count is grouped by the month.

Basically I want to list the total amount of sales for each month. Currently this is what I have:

SELECT MONTH(SaleDate) as "Month", Count(*) as "TotalSales"
FROM Sale
GROUP BY MONTH(SaleDate);

This works great for what I want to do. And gives this results:

+---------------------------------+
|     Month      |   TotalSales   |
+---------------------------------+
|       1        |       123      |
|       2        |       142      |
|       3        |       183      |
|       4        |       99       |
|       5        |       127      |
|       6        |       202      |
+---------------------------------+
etc....

However I want the list to have the months as January, February...etc.

I've tried multiple ways using MONTHNAME(), MONTHNAME(STR_TO_DATE) and many others, but grouping the months seems to cause the issue.

I feel like I'm missing a very simple solution but am unable to find any online. For reference the dates in the Sale table are default (2017-01-01 or YYYY-MM-DD). And the sale table has three attributes: SaleID, StoreID and SaleDate.

+---------------------------------+
|     Month      |   TotalSales   |
+---------------------------------+
|    January     |       123      |
|    February    |       142      |
|     March      |       183      |
|     April      |       99       |
|      May       |       127      |
|     June       |       202      |
+---------------------------------+
and so on...

Here's the error message I recieve from the methods I've tried:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'UsedCarDealer.Sale.SaleDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks!

score 1 · Accepted Answer · answered Oct 16 '19 at 15:42

1

I would suggest:

SELECT MONTHNAME(SaleDate) as "Month", Count(*) as "TotalSales"
FROM Sale
GROUP BY MONTH(SaleDate), MONTHNAME(SaleDate)
ORDER BY MONTH(SaleDate);

This will also return the values in chronological order. Note: When using months, it usually makes sense to include the year as a filter or in the GROUP BY. Perhaps a better version of the query is:

SELECT YEAR(SaleDate) as Year, MONTHNAME(SaleDate) as "Month", Count(*) as "TotalSales"
FROM Sale
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY MIN(SaleDate);

answered Oct 16 '19 at 15:42

Gordon Linoff

1,242,037
58
646
786

i assume you meant `ORDER YEAR(SaleDate), MONTH(SaleDate)` instead as `ORDER BY MIN(SaleDate)` looks a bit odd for the second query? – Raymond Nijland Oct 16 '19 at 15:58
@RaymondNijland . . . Ordering by the `MIN(SaleDate)` does the same thing, but is simpler to type. – Gordon Linoff Oct 16 '19 at 16:04
Sure, but that simple writting style might cause MySQL into doing extra work on the sorting step where `ORDER YEAR(SaleDate), MONTH(SaleDate)` could simply reuse the loaded pages that is what i meant with "odd" – Raymond Nijland Oct 16 '19 at 16:14
@RaymondNijland . . . Interesting point. Implicit sorting with `group by` has been deprecated for a long time. That is to make room for better aggregation algorithms. However, you have a good point that an explicit `order by` using the same keys might make sense because the optimizer could choose the right algorithm. – Gordon Linoff Oct 16 '19 at 16:20
1

*"That is to make room for better aggregation algorithms. However, you have a good point that an explicit order by using the same keys might make sense because the optimizer could choose the right algorithm."* Well i geuss then the MySQL devs have optimisations to work on when seeing this MySQL's 8 [explain format=json](https://www.db-fiddle.com/f/5T3fzRcq6m9Eamvj9jGy3C/9) outputs as `ORDER BY MIN(SaleDate)` seams to have lower expected costs the reverse of what i thought – Raymond Nijland Oct 16 '19 at 17:10
@RaymondNijland . . . That is unexpected on my part. My only guess is that the length of keys somehow filters in and they optimizer is still planning on doing a sort. You made a pretty convincing point before. – Gordon Linoff Oct 16 '19 at 19:55

score 0 · Answer 2 · answered Oct 16 '19 at 15:41

0

Try this:

SELECT MONTHNAME(STR_TO_DATE(MONTH(SaleDate), '%m')) as "Month", Count(*) as "TotalSales"
FROM Sale
GROUP BY MONTH(SaleDate);

answered Oct 16 '19 at 15:41

Nobady

1,074
2
11
35

MySQL = Grouping Sales Count by Months, then Displaying months as Names (Jan, Feb...DEC) not Numerical Value (1,2,3-12)

2 Answers2