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:

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:

Is there a way to do this in one query?

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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