CODEWITHSUNDEEP
sqlsql-server
user342706
user342706

Reputation:

Group by with conditional count

I'm trying to get a count of passing and failing devices I have all the numbers I want, but they aren't quite right.

For example:

select 
    t3.displayname as [PSI], 
    case when t0.compliant = 0 then count(displayname) end as [failures],
    case when t0.compliant = 1 then count(displayname) end as [success]
from lineitemsmap as t0
    inner join art_blob as t1 on t1.art_blob_id = t0.blobid
    inner join art_asset as t2 on t2.art_asset_id = t1.art_asset_id
    inner join net_ou as t3 on t3.net_ouid = t2.net_ouid
group by t3.displayname, t0.compliant

Generates:

------------------------------
| PSI | failures | success   |
------------------------------
| 1   | 3        | NULL      |
------------------------------
| 2   | 4        | NULL      |
------------------------------
| 3   | 5        | NULL      |
------------------------------
| 1   | NULL     | 6         |
------------------------------
| 2   | NULL     | 7         |
------------------------------
| 3   | NULL     | 8         |
------------------------------

I'm trying to get all the results to return in 3 rows verse 6.

Upvotes: 0

Views: 84

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

select 
    t3.displayname as [PSI], 
    SUM(case when t0.compliant = 0 then 1 else 0 end) as [failures],
    SUM(case when t0.compliant = 1 then 1 else 0 end) as [success]
from lineitemsmap as t0
    inner join art_blob as t1 on t1.art_blob_id = t0.blobid
    inner join art_asset as t2 on t2.art_asset_id = t1.art_asset_id
    inner join net_ou as t3 on t3.net_ouid = t2.net_ouid
group by t3.displayname;

Upvotes: 2

Related Questions