首页 文章

JOIN 2表与sum,count,group by

提问于
浏览 1653
1

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

  • 0

    仍然没有跟随你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
    
  • 0

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

相关问题