Problem Age Groups

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

Тагове:

30/09/2017 12:21:42 Databases Basics

melik 285 Точки

Best Answer

https://github.com/melikpehlivanov/Databases-Basics---MS-SQL-Server---2017/blob/master/Data%20Aggregation/Data%20Aggregation%2C%20All%20problems.sql

30/09/2017 12:29:44

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

30/09/2017 13:22:52