SQL Query Return MAX of multiple Counts (Age Groups)-CodePudding

I'm trying to finish this querie to return only the age group that has the most records:

SELECT 
       COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 18 AND 29 THEN a.Data_Nasc END) AS '18-29',
       COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 30 AND 39 THEN a.Data_Nasc END) AS '30-39',
       COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 40 AND 49 THEN a.Data_Nasc END) AS '40-49',
       COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 50 AND 59 THEN a.Data_Nasc END) AS'50-59',
       COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) > 60 THEN a.Data_Nasc END) AS '>60'
FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
WHERE b.NIF_Inst <> 999999999

At this moment the query returns this:

18-29 30-39 40-49 50-59  >60
  9     19   11     5     18

The age group with the most records is 30-39, so it would be just the one that wanted to return.

Can anyone help figure out what the best method to solve?

CodePudding user response：

you can merge the resultsets using UNION and select top most result

SELECT top 1 col1, (col) from (
    SELECT '18-29' as col1,  COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 18 AND 29 THEN a.Data_Nasc END) as col         
    FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
    WHERE b.NIF_Inst <> 999999999
    union all
    SELECT '30-39' as col1, COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 30 AND 39 THEN a.Data_Nasc END) as col
    FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
    WHERE b.NIF_Inst <> 999999999
    union all
    SELECT '40-49' as col1, COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 40 AND 49 THEN a.Data_Nasc END) as col
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
    WHERE b.NIF_Inst <> 999999999
    union all
    SELECT '50-59' as col1, COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 50 AND 59 THEN a.Data_Nasc END) as col    
    FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
    WHERE b.NIF_Inst <> 999999999
    union all
    SELECT '>60' as col1, COUNT(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) > 60 THEN a.Data_Nasc END) as col
    FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
    WHERE b.NIF_Inst <> 999999999) as t
ORDER BY col DESC

CodePudding user response：

SELECT 
       sum(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 18 AND 29 THEN 1 END) AS '18-29',
       sum(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 30 AND 39 THEN 1 END) AS '30-39',
       sum(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 40 AND 49 THEN 1 END) AS '40-49',
       sum(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) BETWEEN 50 AND 59 THEN 1 END) AS'50-59',
       sum(CASE WHEN DATEDIFF(YEAR, a.Data_Nasc, GETDATE()) > 60 THEN 1 END) AS '>60'
FROM Cliente a
    JOIN Encomenda b ON b.NIF_Cliente = a.NIF_Cliente
WHERE b.NIF_Inst <> 999999999