MySQL Group By Query X or not X

user_3852453 0
Viewed 31

This should be easy but I have not found the answer. I have a query that is running a count and I want to group this within the query into those where State = 1 and State <>1 and have the sum for each group.

SELECT count(`id_job`) as count, `state` 
FROM job_table 
GROUP BY `state`;
select mysql sql
4 Answers

You can query a boolean expression and group by it too:

SELECT   state = 1, COUNT(*)
FROM     job_table
GROUP BY state = 1
user_24227760 
SELECT count(id_job) as count,state FROM job_table 
  GROUP BY case when state = 1 then 1 else 0
user_50910730

In MySQL, you can easily put these into columns:

select sum( state = 1 ) as state_1,
       sum( state <> 1 ) as state_2
from job_table;

If state can be NULL, you want to be a bit careful. The second condition is safer as:

select sum( state = 1 ) as state_1,
       sum( not state <=> 1 ) as state_2
from job_table;
user_11440350

Try this

SELECT SUM(CASE WHEN State=1 THEN 1 END) as count1,
       SUM(CASE WHEN State<>1 THEN 1 END) as count2,
       State
FROM job_table 
GROUP BY state
user_49649230
Related