CODEWITHSUNDEEP
mysqlsql
Vinod Nadar
Vinod Nadar

Reputation: 81

MYSQL GROUP BY with different valies in a column

b_id    r_id    charge          amt     status
--------------------------------------------------
1035    1025    Charge1         3       BILLED
1035    1025    Charge2         5       UNBILLED
1035    1025    Charge2         8       BILLED
1035    1025    Charge2         7       BILLED
1035    1025    Charge3         8       PAID
1036    1025    Charge1         9       BILLED
1036    1025    Charge3         7       PAID
1036    1025    Charge3         9       BILLED
1036    1025    Charge2         7       PAID

Above is the sample data, i wanted the output as below.

b_id    r_id    charge          SUM(amt)    status
--------------------------------------------------
1035    1025    Charge1         3           BILLED
1035    1025    Charge2         20          UNBILLED
1035    1025    Charge3         8           PAID
1036    1025    Charge1         9           BILLED
1036    1025    Charge3         16          BILLED
1036    1025    Charge2         7           PAID

Meaning, i have done group by b_id, r_id, charge_id. But for the last column status, the value i want is even if one of the record is UNBILLED, the last column should be unbilled If all records are PAID then show paid in the qroup query else show billed

Upvotes: 0

Views: 40

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

seems you need a specific order by on status
you could use order by field 

    select b_id , r_id , charge , sum(amt),  status 
    from  my_table  
    group by b_id , r_id , charge ,  status 
    order by b_id , r_id , charge , field(status ,'BILLED','UNBILLED', 'PAID')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You seem to want aggregation with conditional logic:

select b_id, r_id, charge,       
       sum(amt),
       (case when sum(status = 'UNBILLED') > 0 then 'UNBILLED'
             when sum(status = 'BILLED') > 0 then 'BILLED'
             when sum(status = 'PAID') > 0 then 'PAID'
        end) as status       
from t
group by b_id, r_id, charge;

Upvotes: 1

Related Questions