Jump to content

Left join and SUM problem


budimir
Go to solution Solved by Barand,

Recommended Posts

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?

 

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.