Софтуерно Инженерство
Loading...
+ Нов въпрос
butoff avatar butoff 33 Точки

Problem Age Groups

Write down a query that creates 7 different groups based on their age

Age groups should be as follows:  [0-10]  [11-20] [21-30] [31-40] [41-50] [51-60] [61+]

Това измислих, но очевидно няма как да работи:

SELECT    CASE
            WHEN [Age] BETWEEN 0 AND 10 THEN '[0-10]'
            WHEN [Age] BETWEEN 11 AND 20 THEN '[11-20]'
            WHEN [Age] BETWEEN 21 AND 30 THEN '[21-30]'
            WHEN [Age] BETWEEN 31 AND 40 THEN '[31-40]'
            WHEN [Age] BETWEEN 41 AND 50 THEN '[41-50]'
            WHEN [Age] BETWEEN 51 AND 60 THEN '[51-60]'
            ELSE '[61+]'
            END AS [AgeGroup],
            COUNT(Age) AS WizardCount
FROM WizzardDeposits
GROUP BY [AgeGroup]

 

В друга тема същото е избрано за най-добър отговор https://softuni.bg/forum/12050/db-fundamentals-data-aggregation-problem-9-age-groups

Тагове:
0
Databases Basics
butoff avatar butoff 33 Точки

Не съм виждал досега вложен SELECT. Това вече има смисъл:

SELECT AgeGroup , COUNT(*) AS WizardCount
FROM (
      SELECT CASE
            WHEN [Age] BETWEEN 0 AND 10 THEN '[0-10]'
            WHEN [Age] BETWEEN 11 AND 20 THEN '[11-20]'
            WHEN [Age] BETWEEN 21 AND 30 THEN '[21-30]'
            WHEN [Age] BETWEEN 31 AND 40 THEN '[31-40]'
            WHEN [Age] BETWEEN 41 AND 50 THEN '[41-50]'
            WHEN [Age] BETWEEN 51 AND 60 THEN '[51-60]'
            ELSE '[61+]'
            END AS [AgeGroup]
      FROM WizzardDeposits) AS FirstSelect
GROUP BY FirstSelect.AgeGroup

0