CODEWITHSUNDEEP
javamysql
Steven Wiaji
Steven Wiaji

Reputation: 44

Select sum condition join table mysql

I want to sum column "sisastock" in "tbpembelian" join with "tbproduk". but my result just one row.

My query :

select 
    pe.idprd,  p.nmprd, sum(pe.sisastock) as 'totalsisastock', 
    pe.tglmasuk, k.idkat, d.iddvs 
from 
    tbproduk as p 
left join 
    tbpembelian as pe on pe.idprd = p.idprd 
join 
    tbkategori as k on p.idkat = k.idkat 
join 
    tbdivisi as d on p.iddvs = d.iddvs 
having 
    sum(pe.sisastock) > 0 
order by 
    pe.tglmasuk DESC

Table "tbproduk"

enter image description here

Table "tbpembelian"

enter image description here

The result I want

enter image description here

Upvotes: 1

Views: 308

Answers (1)

Kandy
Kandy

Reputation: 673

Try This it may help, Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. 

select pe.idprd,  p.nmprd,  pe.tglmasuk, k.idkat, d.iddvs, sum(pe.sisastock) as
'totalsisastock'
from tbproduk as p 
left join tbpembelian as pe on pe.idprd = p.idprd 
join tbkategori as k on p.idkat = k.idkat 
join tbdivisi as d on p.iddvs = d.iddvs 
group by pe.idprd,  p.nmprd,  pe.tglmasuk, k.idkat, d.iddvs
having sum(pe.sisastock) > 0 
order by pe.tglmasuk DESC

Upvotes: 1

Related Questions