CODEWITHSUNDEEP
mysql
Hard worker
Hard worker

Reputation: 4066

How to construct an efficient query that counts different attributes in different columns

I have a table with a row with certain attributes in different columns. Column A can be 1 or 0. Column B can be ``, null or have a value.

I want to count the number of rows in the table that have Column A with a 1, the number of rows in the table that have Column B as `` or null, and the number of rows in Column B that have an entry not equal to '' and not equal to null.

Is it possible to put this all into a single query or do I have to write three different queries?

I am using mysql. Thank you.

Upvotes: 1

Views: 51

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

Your B condition will count all rows anyway so you could do it with a single scan.

SELECT COUNT(CASE WHEN A=1 THEN 'X' END),
       SUM(CASE WHEN B='' OR B IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN B='' OR B IS NULL THEN 0 ELSE 1 END)
FROM YourTable

Upvotes: 1

Related Questions