mysql - JOIN 2表的总和，计数，分组

我想结合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

结果

但我想结合之前的2个查询结果：


丁酮

|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  |

最佳答案

我不是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

关于mysql - JOIN 2表的总和，计数，分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53702469/