query join with sum and count mysql

Question

i have a sample data like this :

trancation-A

=========================
    date        price
=========================
2014-08-16     50000
2014-08-17     60000
2014-08-16     60000

trancation-B

=========================
    date        price
=========================
2014-08-16     75000
2014-08-17     90000
2014-08-18     70000

when parameter date='2014-08-16'

and i want this result like this :

=================================
totalA   priceA   totalB  priceB
=================================
2 Unit   110000   1 Unit  75000

i run my query :

select count(A.price)as totalA, sum(A.price)as priceA, 
count(B.price)as totalB,      sum(B.price)as priceB
from t_penjualan as A join t_pembelian as B
where A.tgl_transaksi = '2014-08-16' and B.tgl_transaksi = '2014-08-16'

still wrong... please help me....

Issues of data display are best resolved in application-level code. More serious is the (apparent) absence of a PRIMARY KEY. — Strawberry
– Strawberry, Commented Aug 16, 2014 at 15:02

Gordon Linoff · Accepted Answer · 2014-08-16 15:03:26Z

1

Do the aggregation before the join:

select totalA, priceA, totalB, priceB
from (select count(A.price)as totalA, sum(A.price)as priceA
      from t_penjualan A
      where A.tgl_transaksi = '2014-08-16'
     ) A cross join
     (select count(B.price)as totalB, sum(B.price)as priceB
      from t_pembelian as B
      where B.tgl_transaksi = '2014-08-16'
     ) B;

answered Aug 16, 2014 at 15:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

query join with sum and count mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related