Performing SQL counts with and without a WHERE clause from the same table

Question

This is my first post so I apologize if I am not concise enough. I am trying to come up with an SQL query to identify data quality issues.

Here's the sample table:

DeviceOS    Bytes  
Roku        10,000  
AppleTV     -50000
SamsungTV   -100000  
Roku        -100000  
AppleTV      30000
Roku        -90000
AppleTV     -20000
AppleTV     -10000
SamsungTV   -100000

Output table:

DeviceOS   Total Count  bad_count
Roku       3            2 
AppleTV    4            2
SamsungTV  1            1   

Total_count field aggregates based on deviceOS and bad_count picks up only all the rows for which megabytes field is negative.

Essentially trying to do this-> select DeviceOS, count(*) from table group by DeviceOS

select DeviceOs, count(*) from table DeviceOS where megabytes < 0

How can I combine the above two queries and have the result of both of them displayed together similar to the output table?

Answer 1

For MySQL use simple

SELECT DeviceOS, 
       COUNT(*) `Total Count`,
       SUM(Bytes < 0) bad_count
FROM source_table
GROUP BY DeviceOS

For BigQuery use

SELECT DeviceOS, 
       COUNT(*) `Total Count`,
       COUNTIF(Bytes < 0) bad_count
FROM source_table
GROUP BY DeviceOS

Answer 2

You can do the following for SQL Server:

SELECT DeviceOS                  AS DeviceOS
     , COUNT(*)                  AS TotalCount
     , SUM(IIF(Bytes < 0, 1, 0)) AS BadCount
FROM (
         VALUES ('Roku', 10000)
              , ('AppleTV', -50000)
              , ('SamsungTV', -100000)
              , ('Roku', -100000)
              , ('AppleTV', 30000)
              , ('Roku', -90000)
              , ('AppleTV', -20000)
              , ('AppleTV', -10000)
              , ('SamsungTV', -100000)
     ) AS t1 (DeviceOS, Bytes)
GROUP BY DeviceOS

We are grouping by the DeviceOS column and getting the Name and the Count.

The only tricky part is using SUM(IIF(Bytes < 0, 1, 0)) AS BadCount to get the bad count.

What this does is it checks if the byte size is less than 0 (meaning it is negative):

• if it is, then +1 is added to the sum
• if it is not, then +0 is added to the sum