How to unify three different queries in mysql when exists multiple criteria?

4
votes

I have a table and I need to get the number of rows using different criteria. Currently I use 3 queries one after another:

  1. SELECT COUNT(status) FROM projects WHERE project='1'
  2. SELECT COUNT(status) FROM projects WHERE project='1' AND status>'10'
  3. SELECT COUNT(status) FROM projects WHERE project='1' AND status>'20'

How do I merge these queries into a single query?

P.S. There are 30 different statuses, so GROUP BY status isn't much of an option.

2
phpmysqlsqldatabase

2 Answers

5
votes

You can use UNION like this:

SELECT COUNT(status), ('1') as info FROM projects WHERE project='1'
UNION
SELECT COUNT(status), ('1-10') as info FROM projects WHERE project='1' AND status>'10'
UNION
SELECT COUNT(status), ('1-20') as info FROM projects WHERE project='1' AND status>'20'

Read more at:

https://dev.mysql.com/doc/refman/4.1/en/union.html

0
votes
SELECT (
        SELECT COUNT(status) FROM projects WHERE project='1'
) AS Count_1,
(
        SELECT COUNT(status) FROM projects WHERE project='1' AND status>'10'
) AS Count_2,
(
        SELECT COUNT(status) FROM projects WHERE project='1' AND status>'20'
) AS Count_3