Mysql – Get distinct count from row and display in sepearte column

countMySQL

I am trying to get count of section type for each section id and course id

My sections table

╔════╤════════════╤═════════╗
║ id │ course_id  │ name    ║
╠════╪════════════╪═════════╣
║ 1  │ 1          │ Quants  ║
╟────┼────────────┼─────────╢
║ 4  │ 1          │ Verbal  ║
╚════╧════════════╧═════════╝

My levels table 

╔════╤════════════╤══════════════╤══════╗
║ id │ section_id │ section_type │ name ║
╠════╪════════════╪══════════════╪══════╣
║ 1  │ 1          │ 1            │ aaa  ║
╟────┼────────────┼──────────────┼──────╢
║ 2  │ 1          │ 1            │ bbb  ║
╟────┼────────────┼──────────────┼──────╢
║ 3  │ 1          │ 2            │ ccc  ║
╟────┼────────────┼──────────────┼──────╢
║ 4  │ 1          │ 1            │ ddd  ║
╚════╧════════════╧══════════════╧══════╝

I need the following ouput

╔════╤════════╤════════════════════════╤═════════════════════════╗
║ id │ name   │ count(section_type =1) │ count(section_type = 2) ║
╠════╪════════╪════════════════════════╪═════════════════════════╣
║ 1  │ Quants │ 3                      │ 1                       ║
╚════╧════════╧════════════════════════╧═════════════════════════╝

This is the query i tried

SELECT a.name, a.id, count( DISTINCT b.section_type )
FROM sections a
INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY b.section_type

Best Answer

The question as I understand it is two-fold:

  1. How to count some value occuring in a separate column
  2. How to autogenerate column list based on result of some query

While 1 is solvable easily, I am quite certain you won't be able to acheive 2.

The solution for 1 could be as following.

Given the test tables and data:

CREATE TABLE sections(
  id INT UNSIGNED PRIMARY KEY,
  course_id INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO sections(id,course_id,name)
VALUES (1,1,'Quants'),
  (4,1,'Verbal');
CREATE TABLE levels(
  id INT UNSIGNED PRIMARY KEY,
  section_id INT UNSIGNED,
  section_type INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO levels(id,section_id,section_type,name)
VALUES (1,1,1,'aaa'),
  (2,1,1,'bbb'),
  (3,1,2,'ccc'),
  (4,1,1,'ddd');

You can get desired output with a:

SELECT a.name, a.id,
  SUM( CASE b.section_type WHEN 1 THEN 1 ELSE 0 END ) as section_type_1,
  SUM( CASE b.section_type WHEN 2 THEN 1 ELSE 0 END ) as section_type_2
FROM sections a INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY a.course_id;
Related Question