CODEWITHSUNDEEP
sqlsql-servercountpivotaggregate-functions
Pr0no
Pr0no

Reputation: 4099

Counting total rows and rows under condition

I have table1 looking like:

docid   val1   val2   val3   value
----------------------------------
001     1      1      null   10
001     null   null   null    5
001     1      null   1      20
001     1      null   null    7
001     null   null   null   15
002     null   null   1      30
002     null   null   null    2

I need as output:

As follows:

docid   total_rows   total_rows_value   rows_with_val   val_rows_value
001     5            57                 3               37
002     2            1                  32              2

What I have until now:

select   [docid],
         count(1) as [rows_with_val],
         sum([value]) as [val_rows_value]
from     table1
where    val1 = 1 or val2 = 1 or val3 = 1
group by [docid]
;

This will only give the hits though. How can I account for both? I understand by deleting the where-clause, but where do I put it then? I have been reading about case statement (in my select) but don't know how to apply it here.

Upvotes: 1

Views: 33

Answers (1)

GMB
GMB

Reputation: 222482

You can use conditional aggregation:

select docid, count(*) total_rows, sum(value) as sum_value,
    sum(case when 1 in (val1, val2, val3) then 1 else 0 end) as cnt_val1,
    sum(case when 1 in (val1, val2, val3) then value else 0 end) as sum_val1
from mytable
group by docid

Upvotes: 1

Related Questions