SQL SERVER Data counting of a record

Question

SQL SERVER Data counting of a record

Navigation

#1 by (4 votes)
#2 by (1 votes)

5

I came across the following situation:

The table below is a checklist and has a column of covenants, the others are questions answered with yes (1), no (0) and noFill (null or 9).

I wanted to tell in each agreement how many of the questions were answered with yes, no or noPrecharging ... at that time no matter the name of the column.

What is the ideal framework for this type of situation or which Query would meet this need.
Then I will group the agreements by region and present the percentage of completion by region.

sql count

asked by anonymous 20.09.2018 / 15:49

2 answers

Is there any way to bend a text with CSS or leave the text bent in a line? on ('change') shoot at every click

score 4 · Answer 1

You can use SUM but you have two points:
- Add all columns
- Group for each yes / no / no fill

It would be possible to do in a single SELECT , adding the columns ( sum(col1)+sum(col2)+... ), but would have the problem of the individual values ... Then I could use a CASE WHEN : ( sum(CASE WHEN col1=1 THEN 1 ELSE 0 END) ) for each value and each column ...

This is an option:

select idConvenio, 
       (SUM(CASE WHEN EnderecoSi=0 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoSit=0 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoContr=0 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoFis=0 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSi=0 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSit=0 THEN 1 ELSE 0 END)) TOT_0
        ,        
       (SUM(CASE WHEN EnderecoSi=1 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoSit=1 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoContr=1 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoFis=1 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSi=1 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSit=1 THEN 1 ELSE 0 END)) TOT_1
        ,
        (SUM(CASE WHEN EnderecoSi=9 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoSit=9 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoContr=9 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN EnderecoFis=9 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSi=9 THEN 1 ELSE 0 END) +
        SUM(CASE WHEN RazaoSit=9 THEN 1 ELSE 0 END)) TOT_9
from exemplo
group by idConvenio

See the fiddle here: link

An observation of CASE WHEN: When it finds a value (0,1 or 9) it returns 1, to add an occurrence, otherwise it returns 0, ignoring.

I've done the example with 6 columns just to speed up, but just add the others, assuming you do not have many.

Another option would be to make two queries, one grouping only yes / no / no fill, put in a CTE and then group again and add.

score 1 · Answer 2

I think you get what you want:

SELECT      iConvenio
        ,   COUNT(1) AS resposta
INTO        #tmpSim
FROM        tabela
WHERE       EnderecoSi      = 1
        OR  EnderecoSit     = 1
        OR  EnderecoContr   = 1
--      OR  ...
GROUP BY    iConvenio

SELECT      iConvenio
        ,   COUNT(1) AS resposta
INTO        #tmpNao
FROM        tabela
WHERE       EnderecoSi      = 0
        OR  EnderecoSit     = 0
        OR  EnderecoContr   = 0
--      OR  ...
GROUP BY    iConvenio

SELECT      iConvenio
        ,   COUNT(1) AS resposta
INTO        #tmpSemPreenchimento
FROM        tabela
WHERE       ISNULL(EnderecoSi, 9)       = 9
        OR  ISNULL(EnderecoSit, 9)      = 9
        OR  ISNULL(EnderecoContr, 9)    = 9
--      OR  ...
GROUP BY    iConvenio

SELECT      iConvenio
        ,   SUM(sim)                AS sim
        ,   SUM(nao)                AS nao
        ,   SUM(sempreenchimento)   AS sempreenchimento
FROM        (
                SELECT  iConvenio
                    ,   resposta    AS sim
                    ,   0           AS nao
                    '   0           AS sempreenchimento
                FROM    #tmpSim
                UNION ALL
                SELECT  iConvenio
                    ,   0           AS sim
                    ,   resposta    AS nao
                    ,   0           AS sempreenchimento
                FROM    #tmpNao
                UNION ALL
                SELECT  iConvenio
                    ,   0           AS sim
                    ,   0           AS nao
                    ,   resposta    AS sempreenchimento
                FROM    #tmpSemPreenchimento
            ) X
GROUP BY    iConvenio

If you do not want (or can not) use temporary tables, you can always pass all that code into SUBSELECT of FROM .