How to check if all instances of a DB row are the same and if so count + 1 in SQL?

I have the following SQLite DB table:

app | vendor | active
---------------------
123 |  AGX   | 1
123 |  OTMA  | 0
123 |  PEI   | 0

255 |  IYU   | 0
255 |  MAG   | 0
255 |  MEI   | 0

675 |  IUU   | 0
675 |  AGU   | 0
675 |  O3I   | 0

I need to be able to run a query to find out out of these apps, which ones have NO active vendors.

In the above example, app 255 and app 675 have no active vendors (0), therefore the return of my query should be 2.

So far, I am only able to run a query to get the number of rows that are ‘false’, but not the number of apps which are false in ALL instances:

SELECT COUNT(*) FROM app_vendor WHERE active = 0

summing up: if all rows of a single app are ‘false’, that should add 1 to the count.

Answer

This query will give you a list of apps with NO active vendors:

SELECT app
FROM app_vendor
GROUP BY app
HAVING SUM(CASE WHEN active = true THEN 1 ELSE 0 END) = 0

To count the records you could wrap it in a subquery:

SELECT COUNT(*) FROM (
  SELECT app
  FROM app_vendor
  GROUP BY app
  HAVING SUM(CASE WHEN active = true THEN 1 ELSE 0 END) = 0
)

Advertisement

Answer