I'm trying to group on a column, and then get the count of specific entries from another column for each group. So in the example below, I want to group by the name and commission, and within those groups, I want the counts of accepted and initials.
Example Table:
name status commission
CashJar Accepted 100.00
CashJar Initial 100.00
CashJar Accepted 100.00
CashJar Accepted 100.00
CashJar Accepted 100.00
CashJar Initial 100.00
CashJar Initial 115.00
CashJar Accepted 115.00
Result:
name count_acc count_init commission
CashJar 4 2 100
CashJar 1 1 115
What I was trying to do was:
Select name,
count(case when status = 'Accepted' then 1 else 0 end) as count_acc,
count(case when status = 'Initial' then 1 else 0 end) as count_init,
commission
from table
group by name, commission
My actual queries are a lot more complicated, but I think the above explains my problem. I'll include my full query below in case the issue isn't apparent without them:
SELECT lender.name AS name, loan.lender_commission commission,
count(case when promo.status = 'Accepted' then 1 else 0 end) as acc,
count(case when promo.status = 'Initial' then 1 when promo.status = null then 1 else 0 end) as init
FROM cmv_post_result post
LEFT JOIN cmv_post_result_new_loan loan ON loan.fk_request_id = post.request_id
INNER JOIN cmv_post_client_profile client ON post.client_id = client.id
INNER JOIN cmv_post_lender_profile lender ON loan.approved_lender_id = lender.id
LEFT JOIN cmv_post_esig_map map ON map.post_lender_fk_id = lender.id
LEFT JOIN vmcpa_wap_promo_reports promo ON promo.userId = map.esig_user_fk_id AND promo.guid = loan.lender_loan_guid
WHERE true = true AND approved_lender_id NOT IN (0,2) AND request_time between '2012/10/15 00:00:00.000' and '2012/10/16 00:00:00.000' GROUP BY lender_name, lender_commission;
The result is:
name commission acc init
CashJar 100.00 6 6
CashYes 100.00 1 1
LeadFlash1 115.00 1 1
LeadFlash1 130.00 6 6
The problem is that are 14 rows in the table, so the total sum of acc and init above isn't producing what I need
2012-10-15 07:26:57 LeadFlash1 130.00 Initial
2012-10-15 09:09:25 LeadFlash1 130.00 Initial
2012-10-15 09:39:42 LeadFlash1 130.00 Initial
2012-10-15 09:54:20 CashJar 100.00 Accepted
2012-10-15 10:37:14 CashJar 100.00 Accepted
2012-10-15 10:37:14 CashJar 100.00 Initial
2012-10-15 10:37:14 CashJar 100.00 Accepted
2012-10-15 13:11:38 LeadFlash1 130.00 Initial
2012-10-15 14:14:39 LeadFlash1 130.00 Initial
2012-10-15 15:16:28 LeadFlash1 115.00 Initial
2012-10-15 15:59:10 CashJar 100.00 Initial
2012-10-15 15:59:10 CashJar 100.00 Accepted
2012-10-15 16:34:10 LeadFlash1 130.00 Initial
2012-10-15 17:55:59 CashYes 100.00 Accepted
So the result I'm looking for is:
name commission acc init
CashJar 100.00 4 2
CashYes 100.00 1 0
LeadFlash1 115.00 0 1
LeadFlash1 130.00 0 6
How would this normally be accomplished? Hopefully I didn't make that more complicated then it need to be, but I wanted to make sure I provided enough information. If there's more info that you need, please let me know.
