vellattukudy vellattukudy - 7 months ago 9
SQL Question

How to merge multiple sql queries

I have a table named

Users
. I am trying out to grab some data from it with the following query:

select name, lastname, gender, status, count(status = 'Trvl') as TrvlCount,
count(status != 'Trvl') as NotTrvlCount, count(id) as TotalCount
from Users
group by name, lastname, gender, dob;


The expected result would be 7 columns with unique name, lastname,gender, dob (to avoid duplicates) and sum of different statuses. But I cant rectify why all 3 count columns are showing the same value or same sum value?

Any idea guys? Thanks in advance.

Answer

You should use sum if construction:

select name, lastname, gender, status, 
       sum(if(status = 'Trvl',  1, 0)) as TrvlCount,
       sum(if(status != 'Trvl',  1, 0)) as NotTrvlCount,
       count(id) as TotalCount
from Users
group by name, lastname, gender, dob;
Comments