CODEWITHSUNDEEP
phpmysql
Loren Ramly
Loren Ramly

Reputation: 1111

How to sum fields in two table and group by date

How to get SUM fields in two table, I have two table, my table is laporan and laporan_pengeluaran.

table laporan

id  shell    date_created
9   12000    2013-07-01
10  24000    2013-07-01
11  5500     2013-07-02

table laporan_pengeluaran

id  laporan_id  harga
1   9           15000
2   9           29000
3   10          7500
4   10          5000

My question, how to get SUM with join table and group by day, and the relation is laporan.id with laporan_pengeluaran.laporan_id. so i want to get result as below:

c_date_created  c_shell   c_harga
2013-07-01       36000    44000
2013-07-02       5500     12500

Current my query is in below, but not succes :-( , which result in c_shell disorderly

SELECT 
    l.date_created as c_date_created
    SUM(l.shell) as c_shell,
    SUM(lp.harga) as c_harga,
    l.*
    FROM laporan l
    LEFT JOIN laporan_pengeluaran lp ON l.id=lp.laporan_id 
    WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
    GROUP BY l.date_created
    ORDER BY l.date_created ASC

Thanks.

Upvotes: 0

Views: 112

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The problem you are facing is that multiple rows in one table match multiple rows in the second -- so you are getting a cross product for each date.

The solution is to do the aggregation before doing the join:

SELECT l.date_created as c_date_created
       l.c_shell,
       SUM(lp.harga) as c_harga,
FROM (select l.date_created, l.shell as c_shell
      from laporan l
      WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
      group by l.date_created
     ) l LEFT JOIN
     laporan_pengeluaran lp
     ON l.id=lp.laporan_id 
GROUP BY l.date_created
ORDER BY l.date_created ASC;

EDIT:

I see. The join is on the id, not on the date. The above wouldn't even work, because the id is not in the second query. You need to summarize each in a subquery. The second requires joining back to the other table in order to get the date:

SELECT l.date_created as c_date_created
       l.c_shell,
       lp.c_harga,
FROM (select l.date_created, l.shell as c_shell
      from laporan l
      WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
      group by l.date_created
     ) l LEFT JOIN
     (select l.date_created, sum(lp.harga) as c_harga
      from laporan l join
           laporan_pengeluaran lp
           on l.id=lp.laporan_id
      group by l.date_created
     ) lp
     ON l.date_created = lp.date_created 
ORDER BY l.date_created ASC;

Upvotes: 1

Barmar
Barmar

Reputation: 780724

You need to group the second table in a subquery before joining, because it's grouped on a different column.

SELECT l.date_created as c_date_created,
SUM(l.shell) as c_shell,
SUM(lp.c_harga) as c_harga,
l.*
FROM laporan l
LEFT JOIN (SELECT laporan_id,
                  SUM(harga) as c_harga
           FROM laporan_pengeluaran
           GROUP BY laporan_id) as lp
ON l.id = lp.laporan_id
WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
GROUP BY l.date_created
ORDER BY l.date_created ASC

Upvotes: 1

Related Questions