CODEWITHSUNDEEP
mysqlsql
Jugi
Jugi

Reputation: 1254

how to group multiple columns in sql

My table looks like below,

ID  EMP_ID  AMOUNT  MODE
1   E01     1000    CASH
2   E01     2000    CASH
3   E01     3000    DD
4   E01     1000    DD
5   E02     1500    CASH
6   E02     2000    DD

I want to sum the total amount paid by each Emp and also the sum of amount paid based on the MODE. For example, i want the result as '7000' as totalAmount, '3000' as cash, '4000' as DD for E01.

i tried the below query, but not able to get the above result.

select sum(AMOUNT) as totalAmount from fees where EMP_ID = 'E01' group by mop

Can anyone suggest a query to get the above result?

EDIT :

I want the resultant table as below,

    ID  EMP_ID  TOTAL_AMOUNT    CASH_AMOUNT     DD_AMOUNT
    1   E01     7000            3000            4000
    2   E02     3500            1500            2000

Upvotes: 1

Views: 56

Answers (2)

jarlh
jarlh

Reputation: 44766

Use case to SUM cash / dd:

select EMP_ID,
       sum(AMOUNT) as totalAmount,
       sum(case when MODE = 'CASH' then AMOUNT else 0 end) as CASH_AMOUNT,
       sum(case when MODE = 'DD' then AMOUNT else 0 end) as DD_AMOUNT
from fees
group by EMP_ID

Upvotes: 2

Jordi Llull
Jordi Llull

Reputation: 810

Use the SUM function combined with the IF function:

   SELECT EMP_ID,
          SUM(amount) AS TOTA_AMOUNT, 
          SUM(IF(`mode` = 'CASH', amount, 0)) AS CASH_AMOUNT,
          SUM(IF(`mode` = 'DD', amount, 0)) AS DD_AMOUNT
     FROM fees
 GROUP BY emp_id

SQL Fiddle: http://www.sqlfiddle.com/#!9/94a55/1

Upvotes: 1

Related Questions