CODEWITHSUNDEEP
mysqlsql
user934902
user934902

Reputation: 1204

SQL Union query error

Im trying to join two count querys

SELECT COUNT(*) AS total FROM clients WHERE addedby = 1
UNION
SELECT COUNT(*) AS converts FROM clients WHERE addedby = 1 AND status = '6'

What this returns is

total
4
0

this is the correct data, what I was expecting was this 

total     converts
  4           0

Upvotes: 0

Views: 45

Answers (3)

Barranka
Barranka

Reputation: 21047

You don't need a UNION query to do this. SELECT A UNION SELECT B returns the rows of A followed by the rows of B (deduplicated; if you want all rows from both datasets, use UNION ALL).

What you want is something like this:

select 
    (select count(*) from clients where addedby=1) as total,
    (select count(*) from clients where addedby=1 and status='6') as converts

Other way to do this is using a case ... end expression that returns 1 if status='6':

select 
    count(*) from clients, 
    sum(case when status='6' then 1 else 0 end) as converts
from clients

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The simplest way to write this query is as a conditional aggregation:

select count(*) as total, sum(status = '6') as converts
from cleints
where addedby = 1;

MySQL treats booleans as integers with 1 being true and 0 being false. You can just sum of the values to get a count.

Upvotes: 0

Andrew Lazarus
Andrew Lazarus

Reputation: 19302

No UNION needed, do it in one pass.

SELECT COUNT(*) as total,
       SUM(CASE status WHEN '6' THEN 1 ELSE 0 END) as converts
FROM clients;

Upvotes: 2

Related Questions