SQL SERVER Counting the data of a record

Question

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 semPreenchimento(null or 9).

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

What is the ideal structure for this type of situation or which Query would meet this need.
Because later I will group the agreements by region and present the percentage of fulfillment by region.

Answer 1

You can use the SUM but it has two points:
- Add up all columns
- Group for each yes/no/no

You could do it in one SELECT, adding up the columns (sum(col1)+sum(col2)+...), but would have the problem of individual values... Then you could use a CASE WHEN: (sum(CASE WHEN col1=1 THEN 1 ELSE 0 END)) for each value and column...

That 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 here the fiddle working: http://sqlfiddle.com/#! 18/0283c/10

An observation of CASE WHEN: when it finds a value (0.1 or 9) returns 1, to sum an occurrence, but returns 0, ignoring.

I did the example with 6 columns just to speed up, but just add the others, assuming there are not many.

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

Answer 2

I think this way 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 don’t want (or can’t) use temporary tables, you can always pass all that code inside the SUBSELECT of FROM.