SQL filtering on values
I have a table like so:
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
And I would like to write 开发者_JAVA技巧a query so that each record comes up with the number of Major and minor faults per day per person. eg:
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
How do I say that I'd like a count the user id, operation_id, count of faults that = "Major", a count of faults that = "Minor" and grouped by date?
Here is what I tried:
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
EDIT: This worked:
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
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
select userid, operation_id,
sum(case when category = 'Major' then 1 else 0 end) as Major_faults,
sum(case when category = 'Minor' then 1 else 0 end) as Minor_faults,
date
from mytable
group by userid, operation_id, date
Something like this could work
SELECT userid, operation_id,
SUM(
SELECT COUNT(category)
FROM faults F
WHERE CATEGORY = 'Major'
AND F.ID = ID
) AS MajorFaults,
SUM
(
SELECT COUNT(category)
FROM faults F
WHERE CATEGORY = 'Minor'
AND F.ID = ID
) AS MinorFaults,
DATE
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 userid, date, operation_id
精彩评论