SQL - Making 4 new columns in a result from another column-CodePudding

So, I'm making a data base for my college class, it's about a foreign languages school, and I need to ( using a single query ), have a number of people that are attending a certain language class, but it has to be seperated by the age group. For example, this is how the result table should look like:

Language | 14-25 | 25-35 | 35-50 | 50 |

German | 1 | 0 | 0 | 0 |

Italian | 2 | 1 | 0 | 0 |

English | 5 | 0 | 0 | 0 |

I need to do this by joining the tables "Class" that has attributes (Language, Number of students), and "Student" that has attributes (ID, name, surname, age, prior knowledge ( eg. A1, B2, ... ))

So I somehow have to figure out in which age group a certain individual goes to, then if he goes there, increment the number of students for that age group by one.

CodePudding user response：

You can build the sum and group the entries using CASE WHEN, so your query will look like this:

SELECT c.language, 
SUM(CASE WHEN s.age BETWEEN 14 AND 25 THEN 1 ELSE 0 END) AS '14-25',
SUM(CASE WHEN s.age BETWEEN 25 AND 35 THEN 1 ELSE 0 END) AS '25-35',
SUM(CASE WHEN s.age BETWEEN 35 AND 50 THEN 1 ELSE 0 END) AS '35-50',
SUM(CASE WHEN s.age >= 50 THEN 1 ELSE 0 END) AS '50 '
FROM class c 
JOIN student_class sc ON c.language = sc.class_language
JOIN student s ON s.id = sc.student_id
GROUP BY c.language;

You have to take care because as example a person whose age is 25 will be selected in both groups "15-25" and "25-35". If this is not intended, you could do something like this:

...SUM(CASE WHEN s.age BETWEEN 14 AND 25 THEN 1 ELSE 0 END) AS '14-25',
SUM(CASE WHEN s.age BETWEEN 26 AND 35 THEN 1 ELSE 0 END) AS '25-35',
SUM(CASE WHEN s.age BETWEEN 36 AND 50 THEN 1 ELSE 0 END) AS '35-50',
SUM(CASE WHEN s.age > 50 THEN 1 ELSE 0 END) AS '50 '...

Please see the working example here: db<>fiddle

You could add an ORDER BY c.language at the end if you want.

A last note: The column aliases shown here ('14-25' etc.) will not work on every DB type and might be replaced depending on DB type and personal "taste".

CodePudding user response：

Assuming you have a table called something like ClassStudent which is linking the individual students to the class (which you absolutely need to fulfil this requirement)...

SELECT c.Language,
    [14-25] = SUM(IIF(s.age BETWEEN 14 AND 25, 1, 0)),
    [25-35] = SUM(IIF(s.age BETWEEN 25 AND 35, 1, 0)),
    [35-50] = SUM(IIF(s.age BETWEEN 35 AND 50, 1, 0)),
    [50 ] = SUM(IIF(s.age >= 50, 1, 0)),
FROM Class c
INNER JOIN ClassStudent cs ON c.Language = cs.Language /* you need this table */
INNER JOIN Student s ON cs.StudentID = s.ID
GROUP BY c.Language

Here, IIF is like a ternary operator in SQL form, and the SUM lets you count up where the condition is met.