0

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
  • What's the question? – mustaccio Apr 25 '16 at 12:13
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.