How to merge multiple sql queries

Question

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.

The general GROUP BY rule is: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." I.e. I'd try group by name, lastname, gender, status. — jarlh
– jarlh, Commented Apr 26, 2016 at 8:42
COUNT will just count rows, try SUM instead if you're trying to count how many rows match those conditions (it works just because TRUE equals 1 and FALSE equals 0) — golimar
– golimar, Commented Apr 26, 2016 at 8:49

Pavel Zimogorov · Accepted Answer · 2016-04-26 08:52:09Z

2

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;

answered Apr 26, 2016 at 8:52

Pavel Zimogorov

1,45211 silver badges28 bronze badges

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

Comments

Jose Hermosilla Rodrigo · Accepted Answer · 2016-04-26 08:59:27Z

0

select name, lastname, gender, status, 
       (SELECT COUNT() FROM Users WHERE status = 'Trvl') as TrvlCount,
       (SELECT COUNT() FROM Users WHERE status <> 'Trvl') as NotTrvlCount,
       count(id) as TotalCount
from Users
group by name, lastname, gender, dob;

Also you can do it with subqueries.

answered Apr 26, 2016 at 8:59

Jose Hermosilla Rodrigo

3,7136 gold badges25 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to merge multiple sql queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related