CODEWITHSUNDEEP
mysqlsql
Pratik Gujarathi
Pratik Gujarathi

Reputation: 768

how to marge two sql results in one sql query?

I am working on a project where I need to write validation query to validate data.

so I have two tables 1. Input table(raw data) 2. Output table(Harmonized data) Currently, as a validation query, I am using below two queries to fetch results & then copy both results into excel file to validate if there is any difference in data or not.

1 Query

Select Date,sum(Val),sum(Vol)
From Input_table
Group by Date

2 Query

Select Date,sum(Val),sum(Vol)
From Output_table
Group by Date

Is there any way where I can put both these results in one query and also create one calculated column like.... (sum(Input_table.VAL)-sum(Output_table.VAL)) as Validation_Check.

So output will be like:

Date | sum(Input_table.Val) | sum(Output_table.Val) | Validation_Check

thanks.

Upvotes: 0

Views: 259

Answers (5)

Mohammedshafeek C S
Mohammedshafeek C S

Reputation: 1943

If your MySQL Supports FULL JOIN then you can use

SELECT
  IFNULL(a.Date, b.Date) AS Date,
  SUM(IFNULL(a.Val, 0)) AS Input_Val_Sum,
  SUM(IFNULL(b.Val, 0)) AS Output_Val_Sum,
  SUM(IFNULL(a.Val, 0) - IFNULL(b.Val, 0)) AS Validation_Check
FROM Input_table AS a
FULL OUTER JOIN Output_table AS b
  ON a.Date = b.Date
GROUP BY IFNULL(a.Date, b.Date)

Upvotes: 0

beejm
beejm

Reputation: 2481

SELECT Date, SUM(VAL) as SUM_VAL, SUM(VOL) as SUM_VOL, SUM(VAL-VOL) as Validation_Check from
(Select Date,val,vol
From Input_table
UNION ALL
Select Date,val, vol
From Output_table
) X
group by Date

I suggest using a UNION ALL instead of a UNION here since there may be similar results fetched from both queries.

For example, your query 1 has a result like

May 01, 2017 | 5 | 5 and your query 2 has a result with the same values May 01, 2017 | 5 | 5

If you use union, you'd only get 1 instance of

May 01, 2017 | 5 | 5

instead of 2 instances of

May 01, 2017 | 5 | 5

May 01, 2017 | 5 | 5

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

It looks like you need to full join your results like:

select
    ifnull(I.[Date], O.[Date]) as Date,
    I.Val as Input_Val,
    O.Val as Output_Val,
    ifnull(I.Val, 0) - ifnull(O.Val, 0) as Validation_Check
from
 (
    Select Date,sum(Val) as Val,sum(Vol) as Vol
    From Input_table
    Group by Date
 ) as I
 full outer join 
 (
    Select Date,sum(Val) as Val,sum(Vol) as Vol
    From Output_table
    Group by Date
 ) as O on O.[Date] = I.[Date]

Upvotes: 2

Jens
Jens

Reputation: 69440

Use this Statement:

select Date, sum(Val), sum(Vol) from (

    Select Date,Val,Vol
    From Input_table
    union
    Select Date,Val,Vol
    From Input_table
)
Group by Date

This will concat the data of both tables in the inner select and then Group it to one result

Upvotes: 0

iPhantomGuy
iPhantomGuy

Reputation: 250

Use UNION. This will join two query on the condition that the two query have the same datatypes in the columns.

Upvotes: 0

Related Questions