Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Confused by new MySQL 5.7 Requiring All Columns in Group by

 
Bartender
Posts: 1752
17
  • 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

 
Bartender
Posts: 3648
16
Mac OS X Firefox Browser 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
Bartender
Posts: 1752
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, thanks. That helped a lot.

-- mike
 
    Bookmark Topic Watch Topic
  • New Topic