CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 16339

Count column multiple times with different where condition

I have a table that stores an admin_id and comment and I am trying to query it to get a count of the different comments associated with each admin.

I am trying to do this like so:

select admin_id, 
        count(comment like 'Approved the claim for%') as Approved,
        count(comment like 'Rejected the claim for%') as Rejected,
        count(comment like 'Temporarily rejected (24 hour hold) the claim for%') as TempReject, 
        count(comment like 'Permanently rejected the claim for%') as PermReject
from admin_comments
group by admin_id

However this is producing results where the columns Approved, Rejected, TempReject and PermReject are all the same, as well as a row at the top that seems to SUM them all together where the admin_id is NULL:

enter image description here

What do I need to change in my query to get accurate figures for each type of comment grouped by admin_id.

Upvotes: 2

Views: 379

Answers (3)

TheName
TheName

Reputation: 733

 select admin_id, 
        count(case when comment like 'Approved the claim for%' then 1 else null end) as Approved,
        count(case when comment like 'Rejected the claim for%' then 1 else null end) as Rejected,
        count(case when comment like 'Temporarily rejected (24 hour hold) the claim for%' then 1 else null end) as TempReject, 
        count(case when comment like 'Permanently rejected the claim for%' then 1 else null end ) as PermReject
    from admin_comments
 group by admin_id

Upvotes: 1

e4c5
e4c5

Reputation: 53734

COUNT Doesn't do Boolean expressions, so you cannot use LIKE inside COUNT. You have to use CASE/WHEN

select admin_id, 
        count( WHEN comment like 'Approved the claim for%' THEN 1 ELSE NULL END) as Approved,
        count(WHEN comment like 'Rejected the claim for%' THEN 1 ELSE NULL END) ....

Note that it has to be NULL and not 0 because COUNT counts non null values. O is a non null value

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : USE CASE Expression for this purpose

select admin_id, 
    SUM(CASE WHEN comment like 'Approved the claim for%' THEN 1 ELSE 0 END) as Approved,
    SUM(CASE WHEN comment like 'Rejected the claim for%' THEN 1 ELSE 0 END) as Rejected,
    SUM(CASE WHEN comment like 'Temporarily rejected (24 hour hold) the claim for%' THEN 1 ELSE 0 END) as TempReject, 
    SUM(CASE WHEN comment like 'Permanently rejected the claim for%' THEN 1 ELSE 0 END ) as PermReject
from admin_comments
group by admin_id

Upvotes: 1

Related Questions