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

countgoogle-bigquerygroup byMySQL

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?

Best Answer

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
Related Question