Q:

MYSQL: Get result twice based on if statement

i'm trying to get from each department from the dept table, the number of rich AND poor employees from the employee table.
So far I was able to get one of each but can't seem to figure how to get both rich and poor written on two rows for each dept.
SELECT Depts.Department, IF (Employees.Salary>100000, 'Rich', 'Poor'), 
COUNT(*)  FROM `Employees`, `Depts` 
WHERE Depts.Dept = Employees.Dept 
GROUP BY Depts.Department

thanks for any help

A:

including your IF logic in GROUP BY 
SELECT Depts.Department, IF (Employees.Salary>100000, 'Rich', 'Poor'), 
COUNT(*)  FROM `Employees`, `Depts` 
WHERE Depts.Dept = Employees.Dept 
GROUP BY Depts.Department, IF (Employees.Salary>100000, 'Rich', 'Poor')

