当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Get Sum with Join table

问题描述:

How to get SUM query with query JOIN table, I have two table, my table is laporan and laporan_pengeluaran.

table laporan

id name shell

9 loli 12000

10 ardi 24000

11 dian 5500

table laporan_pengeluaran

id laporan_id harga

1 9 15000

2 9 29000

3 10 7500

4 10 5000

5 11 3000

6 11 3000

My question, how to get SUM with join table, which group by id/name in table laporan, so i want to get result as below:

id name shell harga

9 loli 12000 44000

10 ardi 24000 12500

11 dian 5500 6000

Thanks.

网友答案:

You can join the two tables and then sum(harga) to get the result. If you know you will always have laporan values in your laporan_pengeluaran table then you can use an INNER JOIN:

select l.id,
  l.name,
  l.shell,
  sum(lp.harga) harga
from laporan l
inner join laporan_pengeluaran lp
  on l.id = lp.laporan_id
group by l.id, l.name, l.shell

See SQL Fiddle with Demo

If you might have missing laporan values in the laporan_pengeluaran table then use a LEFT JOIN:

select l.id,
  l.name,
  l.shell,
  sum(lp.harga) harga
from laporan l
left join laporan_pengeluaran lp
  on l.id = lp.laporan_id
group by l.id, l.name, l.shell

See SQL Fiddle with Demo

The result is:

| ID | NAME | SHELL | HARGA |
-----------------------------
|  9 | loli | 12000 | 44000 |
| 10 | ardi | 24000 | 12500 |
| 11 | dian |  5500 |  6000 |
网友答案:
SELECT
  laporan.id AS id,
  laporan.name AS name,
  laporan.shell AS shell,
  IFNULL(SUM(laporan_pengeluaran.harga),0) AS harga
FROM laporan
LEFT JOIN laporan_pengeluaran ON laporan.id=laporan_pengeluaran.laporan_id
网友答案:

Try this query

SELECT
    l.id,
    l.name,
    IFNULL(lp.TCount,0) as Total
FROM laporan as l   
LEFT JOIN (
            SELECT 
                laporan_id,
                SUM(harga) as TCount
            FROM    laporan_pengeluaran
            GROUP BY laporan_id
            ) as lp ON lp.laporan_id = l.id
网友答案:

Try this:

SELECT l.id, l.name, l.shell, SUM(lp.harga) harga 
FROM laporan l 
LEFT JOIN laporan_pengeluaran lp ON l.id = lp.laporan_id 
GROUP BY l.id; 

Check this SQL FIDDLE DEMO

Output:

| ID | NAME | SHELL | HARGA |
-----------------------------
|  9 | loli | 12000 | 44000 |
| 10 | ardi | 24000 | 12500 |
| 11 | dian |  5500 |  6000 |
分享给朋友:
您可能感兴趣的文章:
随机阅读: