CODEWITHSUNDEEP
mysqljoinsum
Gpgerhard
Gpgerhard

Reputation: 79

Query with sum and join

I have 2 tabels and want to have the sum of one column from table belegpos. With this query it works good, when in artikel is only one row with modell. When there are 2 rows with the same model it takes the sum x2 ( or by 3 rows x3) 

select bp.modell,sum(bp.quantity) 
from belegpos as bp 
join artikel on bp.modell=artikel.modell 
where  artikel.status=0 
group by bp.modell ;

Upvotes: 0

Views: 38

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use an EXISTS instead of a JOIN:

SELECT modell, SUM(quantity) 
FROM belegpos AS bp
WHERE EXISTS (SELECT 1
              FROM artikel 
              WHERE bp.modell = artikel.modell AND artikel.status=0)
GROUP BY bp.modell;

Upvotes: 1

Priyanshu
Priyanshu

Reputation: 881

select bp.modell,count(bp.quantity) 
from belegpos as bp 
join artikel on bp.modell=artikel.modell 
where  artikel.status=0 
group by bp.modell ;

Upvotes: 0

piotrgajow
piotrgajow

Reputation: 2950

You could use subquery with distinct instead:

select bp.modell,sum(bp.quantity) 
from belegpos as bp 
join (SELECT distinct a.modell from artikel a where a.status=0) a1
  on bp.modell=a1.modell 
group by bp.modell ;

Upvotes: 0

Related Questions