budimir Posted December 3, 2015 Share Posted December 3, 2015 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? Quote Link to comment https://forums.phpfreaks.com/topic/299631-left-join-and-sum-problem/ Share on other sites More sharing options...
Solution Barand Posted December 3, 2015 Solution Share Posted December 3, 2015 I suspect that some of your rows are duplicated because of the joins 1 Quote Link to comment https://forums.phpfreaks.com/topic/299631-left-join-and-sum-problem/#findComment-1527474 Share on other sites More sharing options...
budimir Posted December 3, 2015 Author Share Posted December 3, 2015 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! Quote Link to comment https://forums.phpfreaks.com/topic/299631-left-join-and-sum-problem/#findComment-1527477 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.