Example Query Count

Question

Would anyone have any example of how to make a COUNT in a query, separating the data by EstadoId (as an example).

In my case I have several data in my database, registered by several states, I need a query that performs the count.

How much data do you have in São Paulo, how much data do you have in Rio de Janeiro, etc...

Making the junction of everything and coming the data

SP = 100
RJ = 50

And so successively for the other states.

The variable that stores the data in my database is estadoId.

I use SQL Server.

Query


  SELECT 
    CASE EstadoId
        WHEN 'FCB48BA3-9507-47F9-91F9-00945D7D29A8' THEN 'Maranhão'
        WHEN 'D5975036-F585-45BF-A8B3-16A598F84FD1' THEN 'Amazonas'
        WHEN 'ED003698-0B2F-4A4D-BFC4-1901B6BB9642' THEN 'Sergipe'
        WHEN 'E15EC502-6F75-4EF8-BF29-2721D7E232C8' THEN 'Piauí'
        WHEN '61F6D4DB-A0D7-4964-B712-27B9B67B3055' THEN 'São Paulo'
        WHEN 'A383B807-CBE2-406F-BCD9-2D5866583EE8' THEN 'Espírito Santo'
        WHEN 'BA11DFC1-2051-44C9-9782-338C439F9AE8' THEN 'Acre'
        WHEN 'C96EE991-7928-46CD-960F-416B7E5A062F' THEN 'Mato Grosso'
        WHEN 'A8F719C2-0C5C-422F-A09B-48E48C000226' THEN 'Rio Grande do Norte'
        WHEN '96F72912-C994-4766-9540-4FE4725D1391' THEN 'Roraima'
        WHEN 'ED4A75AE-9B49-43B0-8187-5813C965C5C2' THEN 'Pará'
        WHEN 'FD7EAB07-34F9-4E07-A18F-6180C3C028DD' THEN 'Distrito Federal'
        WHEN 'F2F3A942-2F3D-4DDD-80B3-71E2276B0223' THEN 'Mato Grosso do Sul'
        WHEN '1C50585F-5293-499E-91DE-7952927273C7' THEN 'Rio Grande do Sul'
        WHEN '49AF6EA6-A6B9-4647-8586-7D3D22AC4109' THEN 'Goiás'
        WHEN '6607DD2D-3A24-4ED0-8C54-886139046FFA' THEN 'Ceará'
        WHEN '06745B93-AB09-4865-9157-8A246A1E0F05' THEN 'Tocantins'
        WHEN '3DBAA189-DD6E-4FB4-868E-96CFCBC8D515' THEN 'Santa Catarina'
        WHEN '67F4E360-CDE7-4437-8AAF-9846A3470870' THEN 'Paraná'
        WHEN '1A094EB4-384C-40D1-A254-B4F5F18102B5' THEN 'Amapá'
        WHEN '1F8F07B3-0471-4791-AF20-C61E7DE51587' THEN 'Paraíba'
        WHEN 'B0B9E3E2-DA65-40C4-B001-C756B196D557' THEN 'Rondônia'
        WHEN '9982FA1A-47FB-4042-B8D9-C9F06F116AD9' THEN 'Alagoas'
        WHEN '8852C8D8-F088-406F-B929-DEEB59983C66' THEN 'Minas Gerais'
        WHEN '79F036B0-BC15-4D70-B62A-E60D6437134B' THEN 'Bahia'
        WHEN '058AAF5A-3847-4825-8AE2-E7B4E3BEA5AC' THEN 'Rio de Janeiro'
        WHEN '22BE3B04-1970-417F-A994-F8BB90DCB2F1' THEN 'Pernambuco'
    END AS Title, COUNT(*) AS Count
FROM jud_Processos
WHERE Status = 1 AND Trancado = 1 AND EstadoId IS NOT NULL
GROUP BY EstadoId
ORDER BY EstadoId

Answer 1

You can try the following:

CREATE TABLE temp
(
    estadoId char(02),
    valor int 
)

INSERT INTO temp VALUES ('SP', 1)
INSERT INTO temp VALUES ('SP', 2)
INSERT INTO temp VALUES ('SP', 3)
INSERT INTO temp VALUES ('RJ', 1)
INSERT INTO temp VALUES ('RJ', 2)
INSERT INTO temp VALUES ('XX', 1)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)

SELECT ISNULL(estadoID, ' Total'), COUNT(1)
  FROM temp
 GROUP BY estadoID WITH ROLLUP  
 ORDER BY 1

This will generate the following output.

estadoID       NumeroRegistos
RJ             2 
SP             3 
XX             5 
 Total         10

Alternatively, instead of listing the state in different lines, it presents the state as a column

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT',' + QUOTENAME(c.estadoId) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT ' + @cols + ' FROM
            (
                SELECT estadoId, valor
                  FROM temp
           ) x
            PIVOT 
            (
                 COUNT(valor)
                 FOR estadoID IN(' + @cols + ')
            ) p '

EXECUTE(@query)  

The result will be:

RJ  SP  XX 
2   3   5