Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Confused by new MySQL 5.7 Requiring All Columns in Group by

 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to create a summary report, but the way I would do it, with a GROUP BY one field is no longer supported in MySQL when I have two fields in the SELECT portion. The documentation says I can use ANY_VALUE() to override that behavior, but it doesn't work for me. I still get the error that all columns need to be in the GROUP BY. Thus, my sums are incorrect.

Here's the data, the SQL, and sample output:

Data:

amount,yy,mm
100,2015,Jan
200,2015,Feb
300,2015,Mar
160,2016,Jan
200,2016,Feb
300,2016,Mar
100,2016,Feb
250,2015,Jan
500,2015,Mar

SQL (to make GROUP BY happy - so the query runs...):

SELECT mm, IF(yy='2015', SUM(amount), sum(amount)) as theSum from sales group by mm, yy order by mm

-----------------------------------
(Not correctly summed output):
-----------------------------------


Feb 200
Feb 300
Jan 350
Jan 160
Mar 800
Mar 300


---

I really only want to group by  "mm" (month), but even using ANY_VALUE(), it doesn't work.

Suggestions?

Thanks a lot in advance.

- mike

 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you really need to sum the mm (month), then why you need to get the yy (year)?

group by month and year

select mm, yy, sum(amount) from sales group by mm, yy order by mm, yy

group by just mm

select mm, sum(amount) from sales group by mm order by mm

Edit: if you really need to have the year, you can add "where yy=2015" say for Y2015

 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, thanks. That helped a lot.

-- mike
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic