MySql: Sum of two columns from two different tables

Question

It might be a Repeated Question, but my problem is not solving.

I am trying to determine the Inward and Outward of Item using Date
tables.

In table 1, We will call it A for short. I would have three columns namely A_Dt, A_Na, A_Qty

In table 2, We will call it 'B' for short. I would have three columns namely B_Dt, B_Na,B_Qty

In table 3, We will call it 'C' for short. I would have three columns namely C_Dt, C_Na, C_Qty

Table A and B are inward and C are outward.
I had used the revised query but adding the Third column.

Table Example:

Table :A

    A_Dt         A_Na  A_Qty
    2016-08-01    XY     50
    2016-08-02    XY     100
    2016-08-05    XY     150

Table B:

B_Dt       B_Na  B_Qty
2016-08-01  XY  150
2016-08-03  XY  100
2016-08-04  XY  200

Table C:

C_Dt       C_Na  C_Qty
2016-08-01  XY  150
2016-08-03  XY  100
2016-08-04  XY  200

Expected Output

Date           Inward   Outward
2016-08-01      200      150
2016-08-02      100      0
2016-08-03      100      100
2016-08-04      200      200
2016-08-05      150      0

Well, in this case I should use left join or following query must be worked.

Query:

select t.Dt as Date, sum(t.Qty) as Inward,sum(t.outward) as outward1 from(
    select A_Dt as Dt, A_Na as Na, A_Qty as Qty from a
    union all
    select B_Dt as Dt, B_Na as Na, B_Qty as Qty from b
    union all
    select C_Dt as Dt, C_Na as Na, C_Qty as outward from c
)t
group by t.Dt, t.Na
order by t.Dt;

Following Error:

#1054 - Unknown column 't.outward' in 'field list'

Any thoughts would be great.Thanks in advance.

Did you look at UNION?

Strawberry
– Strawberry

2016-08-19 07:20:13 +00:00
Commented Aug 19, 2016 at 7:20 — Strawberry
– Strawberry, Commented Aug 19, 2016 at 7:20

Ullas · Accepted Answer · 2016-08-19 08:06:30Z

4

First combine both the table data into one by using union all and then find the sum of quantity group by date and Na columns.

Query

select t.Dt, t.Na as Inward, sum(t.Qty) as Outward from(
    select A_Dt as Dt, A_Na as Na, A_Qty as Qty from Table_A
    union all
    select B_Dt as Dt, B_Na as Na, B_Qty as Qty from Table_B
)t
group by t.Dt, t.Na
order by t.Dt;

EDIT

Actually now don't need to combine data of TableC, but you have to use a join.

Query

select t1.Dt, t1.Qty as Inward, coalesce(t2.C_Qty, 0) as Outward from(
    select t.Dt, t.Na, sum(t.Qty) as Qty from(
        select A_Dt as Dt, A_Na as Na, A_Qty as Qty from Table_A
        union all
        select B_Dt as Dt, B_Na as Na, B_Qty as Qty from Table_B
    )t
    group by t.Dt, t.Na
)t1
left join Table_C t2
on t1.Dt = t2.C_Dt
order by 1;

Result

+============+========+=========+
| Dt         | Inward | Outward |
+------------+--------+---------+
| 2016-08-01 | 200    | 150     |
| 2016-08-02 | 100    | 0       |
| 2016-08-03 | 100    | 100     |
| 2016-08-04 | 200    | 200     |
| 2016-08-05 | 150    | 0       |
+============+========+=========+

edited Aug 19, 2016 at 8:06

answered Aug 19, 2016 at 7:23

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Soumya Over a year ago

Thanks It worked. But Now I edited the Query.I need Another Output.Any suggestions for that.

Soumya Over a year ago

No, there is some misunderstanding. I need a Inward for aliases 'Qty' and Outward for Aliases 'Outward'. Please refer the Expected Output. You will get an idea.

Serg · Accepted Answer · 2016-08-19 08:13:08Z

0

Your query needs only a small correction

select t.Dt as Date, sum(t.Qty) as Inward, sum(t.outward) as outward1 from(
   select A_Dt as Dt, A_Na as Na, A_Qty as Qty, 0 as outward from a
   union all
   select B_Dt as Dt, B_Na as Na, B_Qty as Qty, 0 as outward from b
   union all
   select C_Dt as Dt, C_Na as Na, 0 as Qty, C_Qty as outward from c
)t
-- where Na = 'XY'
group by t.Dt, t.Na
order by t.Dt;

edited Aug 19, 2016 at 8:13

answered Aug 19, 2016 at 7:35

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySql: Sum of two columns from two different tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related