Order by SQL Server under Procedure

Question

Hello, I have the following situation:
I need to select the amount of care of people of some age groups and of those who are male and female. I have the following tables:
cadastro (id, natendimento, data, sexo, fokfaixaetaria) and
faixaetaria (id, nomefaixaetaria)
the registration table has the faixaetaria column that is a foreign faixaetaria key. I am currently with the following query:

CREATE PROCEDURE [dbo].[SELECIONAR_NUMERO_ATENDIMENTOS] 
@DATAINICIO DATETIME,
@DATAFIM DATETIME
AS

SELECT

faixaetaria.nomefaixaetaria AS FaixaEtaria,
COUNT(case when sexo = 'Masculino' then 1 end) AS Masculino,
COUNT(case when sexo = 'Feminino' then 1 end) AS Feminino,
COUNT(fokfaixaetaria) AS Total

from cadastro 
left join faixaetaria on faixaetaria.id = cadastro.fokfaixaetaria

WHERE data_dte_cadastro BETWEEN @DATAINICIO AND @DATAFIM 
GROUP BY
faixaetaria.nomefaixaetaria

But during the consultation she does not come organized according to age group, ie, I wanted it to come 0 to 3 - so many male both female and total, 4 to 6 - so many feminine and total masculine, 7 to 12 - so many feminine and total masculine, all in sequence. This does not happen it groups according to what comes first from my table register. I believe that there should be an order by only that I’m not able to put it.

Faixaetary table:

Query execution:

Answer 1

You do not need to create a subquery. SQL Server allows you to include in GROUP BY columns that do not appear in the SELECT statement. Just add the id column in GROUP BY and sort it using the new column included in GROUP BY.

CREATE PROCEDURE [dbo].[SELECIONAR_NUMERO_ATENDIMENTOS] 
@DATAINICIO DATETIME,
@DATAFIM DATETIME
AS

SELECT faixaetaria.nomefaixaetaria AS FaixaEtaria,
       COUNT(case when sexo = 'Masculino' then 1 end) AS Masculino,
       COUNT(case when sexo = 'Feminino' then 1 end) AS Feminino,
       COUNT(fokfaixaetaria) AS Total

FROM cadastro 
LEFT JOIN faixaetaria 
  ON faixaetaria.id = cadastro.fokfaixaetaria

WHERE data_dte_cadastro BETWEEN @DATAINICIO AND @DATAFIM 
GROUP BY faixaetaria.nomefaixaetaria, faixaetaria.id
ORDER BY faixaetaria.id

In response to the comment, yes is possible. Just for example do

CREATE PROCEDURE [dbo].[SELECIONAR_NUMERO_ATENDIMENTOS] 
@DATAINICIO DATETIME,
@DATAFIM DATETIME
AS

SELECT faixaetaria.nomefaixaetaria AS FaixaEtaria,
       COUNT(case when sexo = 'Masculino' then 1 end) AS Masculino,
       COUNT(case when sexo = 'Feminino' then 1 end) AS Feminino,
       COUNT(fokfaixaetaria) AS Total

FROM faixaetaria 
lEFT JOIN cadastro 
  ON cadastro.fokfaixaetaria = faixaetaria.id
 AND data_dte_cadastro BETWEEN @DATAINICIO AND @DATAFIM 
GROUP BY faixaetaria.nomefaixaetaria, faixaetaria.id
ORDER BY faixaetaria.id

To include in the result set a record with the total you can use the ROLLUP/GROUPING SETS doing the following

CREATE PROCEDURE [dbo].[SELECIONAR_NUMERO_ATENDIMENTOS] 
@DATAINICIO DATETIME,
@DATAFIM DATETIME
AS

SELECT CASE WHEN GROUPING(faixaetaria.nomefaixaetaria) = 1 THEN 'Total geral' ELSE faixaetaria.nomefaixaetaria END AS FaixaEtaria,
       COUNT(case when sexo = 'Masculino' then 1 end) AS Masculino,
       COUNT(case when sexo = 'Feminino' then 1 end) AS Feminino,
       COUNT(fokfaixaetaria) AS Total

FROM faixaetaria 
LEFT JOIN cadastro 
  ON cadastro.fokfaixaetaria = faixaetaria.id
 AND data_dte_cadastro BETWEEN @DATAINICIO AND @DATAFIM 
GROUP BY GROUPING SETS((faixaetaria.nomefaixaetaria, faixaetaria.id),())
ORDER BY ISNULL(faixaetaria.id, 99)