CODEWITHSUNDEEP
mysql
Hijibiji
Hijibiji

Reputation: 458

Sum of counts in MySQL

I am trying to count all the cells of columnA and columnB that are not null and has a value. I am getting the counts separately in two queries like this:

SELECT COUNT(id) FROM users where columnA IS NOT NULL AND columnA <>''

This query returns value = 7. And for columnB, I do the same and get value 5 by

SELECT COUNT(id) FROM users where columnB IS NOT NULL AND columnA <>''

But how can I query both of the columns at the same time and return the sum of the counts that is 12?

Upvotes: 0

Views: 40

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28196

This should do the job:

select suma, sumb, suma+sumb sumtotal from (
 select sum(case when colA>'' then 1 end) suma,
        sum(case when colB>'' then 1 end) sumb from users ) u

Since we are testing for colA>'' the condition IS NOT NULL will always be fulfilled and does not need to be tested for.

Upvotes: 1

Ranjit Singh
Ranjit Singh

Reputation: 3735

Find count and add them - use case when to count on some condition

 SELECT COUNT(case when columnA IS NOT NULL AND columnA <>'' then 1 end) +
 COUNT(case when columnB IS NOT NULL AND columnB <>'' then 1 end) FROM users

Upvotes: 1

forpas
forpas

Reputation: 164069

With conditional aggregation:

SELECT SUM(columnA IS NOT NULL AND columnA <>'') + SUM(columnB IS NOT NULL AND columnB <>'')
FROM users

or:

SELECT SUM(COALESCE(columnA, '') <>'') + SUM(COALESCE(columnB, '') <>'')
FROM users

Upvotes: 1

Related Questions