Question 339754 in Mysql Question posted in
The official MySQL documentation can be found here.

how to sum of values from two different rows in mysql

I have two tables A and B as below.
Table A

slip component amount
ss-001 PF 1500
ss-001 PT 200
ss-002 PF 1450
ss-002 PT 150
ss-003 PF 1550
ss-003 PT 300

Table B

emp slip netpay
emp-001 ss-001 18500
emp-002 ss-002 17000
emp-003 ss-003 16600

how may I get below Table through query?

emp PF + PT netpay
emp-001 1700 18500
emp-002 1600 17000
emp-003 1850 16600

I am able to get PF and PT in two different columns but unable to SUM.

I tried SUM() and CONCAT()

Tags:

2

Answers


  1. 0

    This can be done using the conditional aggregation as follows :

    select emp, sum(case when component = 'PF' then amount end) 
            + sum(case when component = 'PT' then amount end) as 'PF + PT', 
            max(netpay) as netpay
from tableA a
inner join tableB b on a.slip = b.slip
group by emp;

    Results :

    emp     PF + PT netpay
emp-001 1700    18500
emp-002 1600    17000
emp-003 1850    16600

    Demo here

    Login or Signup to reply.
  2. 0

    A simple correlated query should be enough:

    select emp, (
    select sum(amount)
    from table_a
    where table_a.slip = table_b.slip
) as "PF + PT", netpay
from table_b
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search