Left join and SUM problem

[budimir]

Hi people,

 

I'm having a problem with my SQL query which is not doing what I would like. I have this query:

SELECT a. id,
       a.kataloski_broj, 
       SUM(a.quantity) AS zbroj, 
       entry_type_desc, 
       posting_date, 
       item_category, 
       product_group,
       b.nabavna_cijena as neto_nabavna,
       b.neto_VPC,
       b.preporucena_VPC,
       c.nabavna_cijena as rabat_nabavna,
       c.VPC,
       d.VPC as NAV_VPC,
       d.PVPC as NAV_PVPC
    FROM prodaja_zaliha a
  LEFT JOIN kalkulacija_stavke b ON a.kataloski_broj = b.kataloski_broj
  LEFT JOIN kalkulacija_stavke_rabat c ON a.kataloski_broj = c.kataloski_broj
  LEFT JOIN katalog_pribora_item d ON a.kataloski_broj = d.kataloski_broj_NAV
    WHERE a.entry_type_desc = 'Prodaja' 
    AND YEAR(a.posting_date) = '2015' 
    AND a.kataloski_broj = 'M1200'
  GROUP BY a.id ASC

The problem is that it returns SUM(a.quantity) AS zbroj in 1432 quantity, while there is actually 182 quantity in DB.

 

What am I doing wrong?

 

 

[Barand]

I suspect that some of your rows are duplicated because of the joins

[budimir]

Barand,

 

As always first on my posts and most willing to help. Thanks for you're reply Barand! I have managed to figure out my problem while I was waiting for help. You are right, my rows are getting duplicate because of joins, so I have reworked the query and now it's working and it looks like this.

SELECT ABS(SUM(quantity)) as zbroj, a.kataloski_broj, a.id, a.entry_type_desc, a.posting_date, a.item_category, a.product_group
  FROM prodaja_zaliha a
  LEFT JOIN (SELECT kataloski_broj, nabavna_cijena, neto_VPC, preporucena_VPC
  FROM kalkulacija_stavke
  GROUP BY kataloski_broj) AS b
  ON a.kataloski_broj = b.kataloski_broj
  LEFT JOIN (SELECT kataloski_broj, nabavna_cijena, VPC
    FROM kalkulacija_stavke_rabat
    GROUP BY kataloski_broj) AS c
  ON a.kataloski_broj = c.kataloski_broj
  LEFT JOIN(SELECT kataloski_broj_NAV, VPC, PVPC
    FROM katalog_pribora_item
    GROUP BY kataloski_broj_NAV) AS d
  ON a.kataloski_broj = d.kataloski_broj_NAV
WHERE a.kataloski_broj = 'M1200' AND
  a.entry_type_desc = 'Prodaja' AND
YEAR(a.posting_date) = '2015'

I need to style it a bit, but it's finally giving correct result.

 

Barand, thank you for reply!