Avatar of ritterskampj
ritterskampj
 asked on

SQL counting instances of values

I am using MYSQL, I am using this query:

SELECT students.grade, students.homeroom, orders.meal_quantity, orders.option_choice, orders.drink_choice
FROM orders left join students
on students.id = orders.meal_ordered_for
WHERE `order_for_day` = '2013-04-03'
ORDER BY students.grade, students.homeroom ASC

Open in new window


This gives me the raw data I need, show here:
My Current Query Results

However, I would like to further organize the data by grouping the results by homeroom and giving totals for quantity, and each option and drink, like this:

What I'd like my results to be formatted like

Is there a way to do this in one query?
MySQL ServerSQL

Avatar of undefined
Last Comment
Sharath S

8/22/2022 - Mon
Sharath S

Is this the complete set or do you have more option_choice and drink_choice? The reason why I am asking this question is, those will be column names in your expected result. If you have more choices, then you will have more columns.
ASKER CERTIFIED SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sharath S

Here is a dynamical way of doing the same.
SELECT GROUP_CONCAT(distinct 'SUM(CASE WHEN option_choice = ''',option_choice,''' THEN meal_quantity END) AS ''',option_choice,'''')
  INTO @sql1 
  FROM Test;

SELECT GROUP_CONCAT(distinct 'SUM(CASE WHEN drink_choice = ''',drink_choice,''' THEN meal_quantity END) AS ''',drink_choice,'''')
  INTO @sql2 
  FROM Test;
  
SELECT CONCAT('select Grade,homeroom,sum(meal_quantity) as meal_quantity,',
              @sql1,',',@sql2,
              ' from Test 
               group by Grade,homeroom;') 
  INTO @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt; 

Open in new window


Tested here: http://sqlfiddle.com/#!9/3bd68/17
Sharath S

If you want to replace NULL values with 0.
SELECT GROUP_CONCAT(distinct 'SUM(CASE WHEN option_choice = ''',option_choice,''' THEN meal_quantity ELSE 0 END) AS ''',option_choice,'''')
  INTO @sql1 
  FROM Test;

SELECT GROUP_CONCAT(distinct 'SUM(CASE WHEN drink_choice = ''',drink_choice,''' THEN meal_quantity ELSE 0 END) AS ''',drink_choice,'''')
  INTO @sql2 
  FROM Test;
  
SELECT CONCAT('select Grade,homeroom,sum(meal_quantity) as meal_quantity,',
              @sql1,',',@sql2,
              ' from Test 
               group by Grade,homeroom;') 
  INTO @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;

Open in new window


Tested here: http://sqlfiddle.com/#!9/3bd68/21
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23