CODEWITHSUNDEEP
phpmysqlsql
xitas
xitas

Reputation: 1164

Get Records of two tables with same date

I am stuck in a problem. After trying for hours I could not get to a proper solution.

So first What am I trying to do: I have two tables of sales and purchase both have data according to dates.

Data is like this in mysql:

Sale:

  sale1  |  25-07-14  | 2500
  sale2  |  26-07-14  | 2200
  sale3  |  27-07-14  | 0
  sale4  |  27-07-14  | 1500
  sale5  |  28-07-14  | 1000

Purchase:

  purchase1  |  25-07-14  | 0
  purchase2  |  26-07-14  | 1000
  purchase3  |  27-07-14  | 2500
  purchase4  |  28-07-14  | 0
  purchase5  |  28-07-14  | 100

Now I want to show my data like this:

Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)

I want data with respect to date from both table.

this is what I have tried:

SELECT date, sum(purchase_remaining) as a FROM purchase group by date
UNION
SELECT sale_date, sum(sale_balance) as b FROM sale group by sale_date

Result of this query: 

date | purchase
date | purchase
date | purchase
date | sale
date | sale
date | sale

Any idea how can I achieve this.

Upvotes: 4

Views: 1074

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Join the tables.

select date, sum(purchase_remaining) a
, sum(sale_balance) b

from purchase join sale on purchase.date = sale.date
group by date

Upvotes: 3

Related Questions