MySQL Using CASE WHEN to retreive specific columns of data matching certain conditions

I'm using the following SQL to try to retrieve data for a particular Grower for all weeks from the last four years.

SELECT fus.fusWeek,
CASE WHEN fus.fusYear = 2012 THEN fus.fusUsage ELSE 0 END AS Usage2012,
CASE WHEN fus.fusYear = 2013 THEN fus.fusUsage ELSE 0 END AS Usage2013,
CASE WHEN fus.fusYear = 2014 THEN fus.fusUsage ELSE 0 END AS Usage2014,
CASE WHEN fus.fusYear = 2015 THEN fus.fusUsage ELSE 0 END AS Usage2015
FROM wp_mbtd_fuel_use fus
WHERE fus.fusGrowerID = 'A' GROUP by fus.fusWeek

There is data for many growers, for every week of the year for every week.

However when I execute this SQL, I only get data for 2014 !!!!


Would love some help with this to get data for ALL YEARS as a single result
sql-structure.PNG
sql-results.PNG
sql-data-2012-2013.PNG
LVL 1
Sean ClarkeOwner / ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

F IgorDeveloperCommented:
You are grouping only for fus.fusWeek, then fus.fusYear and fus.fusUsage are not single values (your results only show a single row for each group). You need to sum these values as total usage for each year

SELECT fus.fusWeek,
SUM(CASE WHEN fus.fusYear = 2012 THEN fus.fusUsage ELSE 0 END) AS Usage2012,
SUM(CASE WHEN fus.fusYear = 2013 THEN fus.fusUsage ELSE 0 END) AS Usage2013,
SUM(CASE WHEN fus.fusYear = 2014 THEN fus.fusUsage ELSE 0 END) AS Usage2014,
SUM(CASE WHEN fus.fusYear = 2015 THEN fus.fusUsage ELSE 0 END) AS Usage2015
FROM wp_mbtd_fuel_use fus
WHERE fus.fusGrowerID = 'A' 
GROUP by fus.fusWeek

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sean ClarkeOwner / ManagerAuthor Commented:
Genius - thank you - obvious, but clearly not that obvious :-)
0
ste5anSenior DeveloperCommented:
When you use a column in MySQL in a GROUPed statement and this column is not part of the GROUP BY clause, then MySQL returns the first value of that column. Thus is the reason why your SQL works.

In other dialects this kind of query would throw an error, that you need either to use that column in the GROUP BY clause or in an aggregate as fraigor already showed you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.