mysql - SQL 过滤值

Question

我有一个这样的表:

id | userid | operation_id | category | date
===============================================
1   bobdole   2              Major      2011-02-20
2   bobdole   3              Major      2011-02-20
3   bobdole   2              Minor      2011-02-20
4   bobdole   2              Minor      2011-02-20
5   bobdole   2              Minor      2011-02-21
6   not_bob   2              Minor      2011-02-21

并且我想编写一个查询，以便每条记录得出每人每天的主要和次要故障的数量。例如:

userid  | operation_id | Major faults| Minor faults | date
=================================================================
bobdole   2              1            2             2011-02-20
bobdole   3              1            0             2011-02-20
bobdole   2              0            1             2011-02-21
not_bob   2              0            1             2011-02-21

如何表示我想要一个用户 ID、operation_id、=“主要”的故障计数、=“次要”的故障计数并按日期分组？

这是我尝试过的:

SELECT employee, operation_id, date_entered, orgin, grading, 
       COUNT(grading = "Major"), count(grading = "Minor") 
  FROM faults 
  JOIN company.tblusers on faults.employee = compeny.tblusers.sugar_name 
 WHERE ncr_ncr.date_entered > date("2011-01-01")
   AND protocase.tblusers.departmentid = 8
   AND orgin != "unknown" 
   AND orgin != "Unknown"
   AND orgin IS NOT NULL
GROUP BY employee, date_entered, operation_id

编辑:这有效:

select 
  employee, 
  userid,
  operation_id, 
  date(date_entered), 
  orgin,  
  SUM(case when grading = 'Major' then 1 else 0 end), 
  SUM(case when grading = 'Minor' then 1 else 0 end) 
from ncr_ncr 
join company.tblusers on ncr_ncr.employee = company.tblusers.sugar_name 
where 
ncr_ncr.date_entered > date("2011-01-01") 
and company.tblusers.departmentid = 8
and orgin != "unknown" 
and orgin != "Unknown"
and date_entered is not null
and orgin is not null
AND(grading = "Minor" or grading = "Major")
group by employee, date(date_entered), operation_id
order by date(date_entered), employee, operation_id

Answer 1

select 
  userid, operationid,
  sum(case category when 'Major' then 1 else null end) MajorFaults,
  sum(case category when 'Minor' then 1 else null end) MinorFaults,
  date
from YourTable
group by userid, operationid, date