Order by SQL Server in Procedure

Question

Order by SQL Server in Procedure

Navigation

#1 by (3 votes)

1

Hello, I am in 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 table registry has the column array that is the foreign key of the registry. I currently have 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 it is not organized according to the age group, that is, I wanted it to come 0 to 3 - so many male so many females and total, 4 to 6 - so many masculine so many feminine and total, 7 to 12 - so many male so many female and total, everything in sequence. That does not happen he groups according to what comes first from my registration table. I believe that there should be an order by only that I am not able to put it.

Table list:

QueryExecution:

sql sql-server procedure query order-by

asked by anonymous 02.09.2015 / 21:31

1 answer

Php and guzzle, how to do a get to an address and get what to return? CSS - how to do all-level numbering in nested lists

score 3 · Accepted Answer

You do not need to create a subquery. SQL Server allows you to include columns in the GROUP BY 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 it is possible. Simply, 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 a record with the total in the result set you can use ROLLUP / GROUPING SETS by 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)