MYSQL multiple COUNT() with multiple conditions in single query

Question

I'm newbie in MYSQL.

I have a table like below:

colmnA    colmnB
A           x
A           y
B           x
B           z
C           x
D           null

What I want to achieve is something like:

SELECT
COUNT(distinct colmnA) WHERE colmnB IS NOT NULL AS deployed,
COUNT(distinct colmnA) WHERE colmnB IS NULL AS undeployed
FROM table

which will be resulted in:

deplyed    undeployed
3          1

is there an elegant way to achieve this with a single query? thanks for help

As far as I've searched, the most closed solution is combining SUM() with CASE condition, ex:

SELECT SUM(CASE WHEN columnB IS NOT NULL then 1 else 0) AS deployed

but it will include duplicated count, and there is no way to add DISTINCT into SUM().

JRD · Accepted Answer · 2015-11-10 05:33:36Z

1

Why not use count?

SELECT COUNT(DISTINCT CASE WHEN colmnB IS NOT NULL THEN colmnA END) deployed,
       COUNT(DISTINCT CASE WHEN colmnB IS NULL THEN colmnA END) undeployed
FROM   table;

answered Nov 10, 2015 at 5:33

JRD

1,9871 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jin Ming - 金明 - 김명 Over a year ago

Thanks! it's working! and I apologies for my poor searching skill -_-

Darwin von Corax · Accepted Answer · 2015-11-10 05:49:23Z

0

select max(deployed) as deployed, max(undeployed) as undeployed from ( select count(distinct columnA) as deployed, 0 as undeployed from testtable where columnb is not null union select 0 as deployed, count(distinct columnA) as undeployed from testtable where columnb is null ) as foo;

@JRD's answer works too.

answered Nov 10, 2015 at 5:49

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Collectives™ on Stack Overflow

MYSQL multiple COUNT() with multiple conditions in single query

which will be resulted in:

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

which will be resulted in:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related