Example Query Count

Navigation
1

Someone would have an example of how to do a COUNT in a query, separating the data by EstadoId (as an example).

In my case I have several data in my bank, registered by several states, I need a query that counts.

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

Matching everything and coming the data 

SP = 100
RJ = 50

And so on to 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
    
asked by anonymous 14.12.2017 / 14:55

1 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 rows, it displays the state as 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
    
14.12.2017 / 14:58
6 build path entries are missing in eclipse Android Studio and Google Maps API - Change Travel Mode