JOIN 2表与sum，count，group by-Java 学习之路

我想结合2个查询来制作一个表 . 这是我的查询：

第一：

SELECT namas, SUM(jumlahtransaksi) as sum, COUNT(statustransaksi) as count
FROM dbtransaksi
where statustransaksi='Tertagih'
GROUP BY namas

第二：

SELECT jeniskegiatan, namas, COUNT(jeniskegiatan)
FROM dbkegiatan
GROUP by jeniskegiatan, namas

我的查询不起作用，我不确定为什么

SELECT dbkegiatan.namas, dbkegiatan.jeniskegiatan,
       COUNT(dbkegiatan.jeniskegiatan) as count1, dbtransaksi.namas,
       sum(dbtransaksi.jumlahtransaksi) as sum, COUNT(dbtransaksi.statustransaksi) as count
from dbkegiatan
    left outer join dbtransaksi on dbkegiatan.namas=dbtransaksi.namas
where dbtransaksi.statustransaksi='Tertagih'
group by dbkegiatan.namas, dbkegiatan.jeniskegiatan, dbtransaksi.namas

结果
from my query
但我想结合前面的2个查询结果：
first result

and second result to be one table
dbkegiatan

|id_keg | nip   | namas | jeniskegiatan  |
|KEG001 | 203004| Amin  |Visit/Presentasi|
|KEG002 | 203004| Amin  |Visit/Presentasi|
|KEG003 | 203005| Budi  |Demo Produk     |
|KEG004 | 203006| Cecep |Lost Project    |
|KEG005 | 203007| Dedi  |Customer Baru   |
|KEG006 | 203004| Amin  |Demo Produk     |
|KEG007 | 203005| Budi  |Visit/Presentasi|
|KEG008 | 203006| Cecep |Demo Produk     |
|KEG009 | 203007| Dedi  |Lost Project    |
|KEG010 | 203004| Amin  |Cross/Up Selling|

dbtransaksi

|id_trans| nip   | namas | jumlahtransaksi | statustransaksi|
|TRA001  |0203004| Amin  |250000000        |Tertagih        |
|TRA002  |0203005| Budi  |400000000        |Tertagih        |
|TRA003  |0203005| Budi  |250000000        |Tertagih        |
|TRA004  |0203006| Cecep |80000000         |Tertagih        |
|TRA005  |0203007| Dedi  |128000000        |Tertagih        |
|TRA006  |0203007| Dedi  |452500000        |Belum Tertagih  |
|TRA007  |0203004| Amin  |250000000        |Belum Tertagih  |

2 回答

仍然没有跟随你100％是你试图加入表格，如果是这样，以下应该工作

SELECT '' AS jeniskegiatan namas, SUM(jumlahtransaksi) as sum, COUNT(statustransaksi) as count
FROM dbtransaksi
where statustransaksi='Tertagih'
GROUP BY namas

UNION

SELECT jeniskegiatan, namas, '' as sum, COUNT(jeniskegiatan) AS count
FROM dbkegiatan
GROUP by jeniskegiatan, namas

我不是100％肯定你想要达到的目标，但我认为这就是你所追求的

SELECT dbkegiatan.namas, dbkegiatan.jeniskegiatan,
           COUNT(dbkegiatan.jeniskegiatan) as count1, dbtransaksi.namas,
           sum(dbtransaksi.jumlahtransaksi) as sum, COUNT(dbtransaksi.statustransaksi) as count
    from dbkegiatan
        left outer join dbtransaksi on dbkegiatan.namas=dbtransaksi.namas AND dbtransaksi.statustransaksi='Tertagih'
    group by dbkegiatan.namas, dbkegiatan.jeniskegiatan, dbtransaksi.namas

JOIN 2表与sum，count，group by

2 回答

相关问题