Reputation:
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
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