CODEWITHSUNDEEP
mysqljoininner-join
user1354610
user1354610

Reputation: 1

Inner self-join with counts based on different criteria

I have a table that partially looks like:

CustomerID — the key
Employee — the employee that signed up the customer   
StartDate — the date someone became a customer    
Status — the status of the customer, represented by an integer.

There's one integer, -5, that's "bad" and 4 other integers are differing stages of "good". If they've proven to be fraudulent, they get a -5.

I want a query that returns something like:

Employee   |  FRAUDULENT | NOT FRAUDULENT  | TOTAL CUSTOMERS
-------------------------------------------------------------
mshreck            100             37               137
fwmurnau            27             10                37

So each row contains a count of records after a certain startDate that have been marked fraudulent (have a value of -2), not fraudulent (anything other than -2), the same of the two. And I want a row for each Employee.

Right now, I have to run two queries and use Excel to join them together. I haven't used inner joins on a table itself, but this feels like it would be a possible solution.

Any help would be greatly appreciated.

Upvotes: 0

Views: 83

Answers (2)

Shadow
Shadow

Reputation: 34285

No, you do not need self join, just use sum() insted of count() with some tweaks to get the numbers:

select employee, sum(if(status=-5,1,0)) as fraudulent, sum(if(status<>-5,1,0)) as notfraudulent, count(*) as total
from table group by employee
where stardate>...

Upvotes: 0

trincot
trincot

Reputation: 350760

SELECT   Employee, 
         SUM(CASE WHEN Status = -2 THEN 1 END) AS FRAUDULENT 
         SUM(CASE WHEN Status <> -2 THEN 1 END) AS NOT_FRAUDULENT
         COUNT(*) AS TOTAL_CUSTOMERS
FROM     MyTable
WHERE    StartDate >= :mydate
GROUP BY Employee;

Upvotes: 1

Related Questions