Senthilkumar Posted July 14, 2023 Share Posted July 14, 2023 Dear Team, I am having two different tables dbms.tiv_data & dbms.currentyeardata. Wheni am using sum function on bothe the tabel seperatly, it is working fine select Category,sum(Quantity) as Actual from dbms.tiv_data WHERE Brand='XCMG' group by Category the output is select Category, sum(Total) as Target from dbms.currentyeardata group by Category The output is But wheni am using inner join query , i am getting wrong output SELECT dbms.tiv_data.Category, sum(dbms.tiv_data.Quantity) AS Actual,sum(dbms.currentyeardata.Total) AS Target from dbms.currentyeardata Inner join dbms.tiv_data ON dbms.tiv_data.Dealerid = dbms.currentyeardata.Dealer_ID WHERE dbms.tiv_data.Dealerid = '81019218' AND dbms.tiv_data.Brand = 'XCMG' group by dbms.tiv_data.Category order by Actual ASC THe worng output is Can any one please correct where i am doing mistake on this query Quote Link to comment Share on other sites More sharing options...
Barand Posted July 14, 2023 Share Posted July 14, 2023 If we start with this data currentyeardata (targets) tiv_data (actuals) +----+-----------+----------+-------+ +----+-----------+----------+-------+----------+ | id | category | dealerid | total | | id | category | dealerid | brand | quantity | +----+-----------+----------+-------+ +----+-----------+----------+-------+----------+ | 1 | EXCAVATOR | 81019218 | 100 | | 1 | EXCAVATOR | 81019218 | XCMG | 25 | | 2 | GRADER | 81019218 | 150 | | 2 | GRADER | 81019218 | XCMG | 50 | +----+-----------+----------+-------+ +----+-----------+----------+-------+----------+ then run your query SELECT tiv.Category , sum(tiv.Quantity) AS Actual , sum(cyd.Total) AS Target FROM currentyeardata cyd Inner join tiv_data tiv ON tiv.Dealerid = cyd.DealerID WHERE tiv.Dealerid = '81019218' AND tiv.Brand = 'XCMG' GROUP BY tiv.Category ORDER BY Actual ASC; +-----------+--------+--------+ | Category | Actual | Target | +-----------+--------+--------+ | EXCAVATOR | 50 | 250 | | GRADER | 100 | 250 | +-----------+--------+--------+ These are obviousy wrong, as you are experiencing. Let's look at the actual rows that query selects without the grouping/aggregations but using your join SELECT * FROM currentyeardata cyd Inner join tiv_data tiv ON tiv.Dealerid = cyd.DealerID WHERE tiv.Dealerid = '81019218' AND tiv.Brand = 'XCMG'; +----+-----------+----------+-------+----+-----------+----------+-------+----------+ | id | category | dealerid | total | id | category | dealerid | brand | quantity | +----+-----------+----------+-------+----+-----------+----------+-------+----------+ | 1 | EXCAVATOR | 81019218 | 100 | 1 | EXCAVATOR | 81019218 | XCMG | 25 | | 1 | EXCAVATOR | 81019218 | 100 | 2 | GRADER | 81019218 | XCMG | 50 | | 2 | GRADER | 81019218 | 150 | 1 | EXCAVATOR | 81019218 | XCMG | 25 | | 2 | GRADER | 81019218 | 150 | 2 | GRADER | 81019218 | XCMG | 50 | +----+-----------+----------+-------+----+-----------+----------+-------+----------+ Beacuse you join on dealerid, each target record joins with two actual records so you are totalling twice as many records as you expected. You need to join on category and dealerid... SELECT tiv.Category , sum(tiv.Quantity) AS Actual , sum(cyd.Total) AS Target FROM currentyeardata cyd Inner join tiv_data tiv USING (category, dealerid) -- REVISED JOIN WHERE tiv.Dealerid = '81019218' AND tiv.Brand = 'XCMG' GROUP BY tiv.Category ORDER BY Actual ASC; +-----------+--------+--------+ | Category | Actual | Target | +-----------+--------+--------+ | EXCAVATOR | 25 | 100 | | GRADER | 50 | 150 | +-----------+--------+--------+ Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted July 19, 2023 Author Share Posted July 19, 2023 Dear Barand, when i am running select * from query on tiv_data it returns total 11 rows When i am running select * from query on currentyear data it returns total 20 rows When i am running the inner join query as per your suggession i am getting total 90 row Because of this the sum value is updating wrong. Can you please suggest me what to do for this problem Quote Link to comment Share on other sites More sharing options...
Barand Posted July 19, 2023 Share Posted July 19, 2023 You may have to resort to subqueries subquery 1 - total actual for each category subquery 2 - total target for each category join the subqueries using category to get final result. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted July 19, 2023 Author Share Posted July 19, 2023 Dear Barand, I am trying this bellow subquery select Category,sum(Quantity) as Actual from dbms.tiv_data WHERE Brand='XCMG' AND Category = (select Category, sum(Total) as Target from dbms.currentyeardata WHERE Dealerid = '81019218' group by Category); But i am getting error "Error Code: 1241. Operand should contain 1 column(s)" Quote Link to comment Share on other sites More sharing options...
Barand Posted July 19, 2023 Share Posted July 19, 2023 I what universe is that two subqueries joined on category? SELECT act.category , target , actual FROM ( -- subquery 1 SELECT category , sum(quantity) as actual FROM tiv_data WHERE brand = 'XCMG' AND dealerid = 81019218 GROUP BY category ) act JOIN ( -- subquery 2 SELECT category , sum(total) as target FROM currentyeardata WHERE dealerid = 81019218 GROUP BY category ) targ USING (category); Quote Link to comment Share on other sites More sharing options...
Solution Senthilkumar Posted July 19, 2023 Author Solution Share Posted July 19, 2023 Dear Barand, Thanks for your support. It is working now. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 2, 2023 Author Share Posted August 2, 2023 Dear Barand, I tried the same query for another table. But the output is not working. My query is SELECT act.Revenue_Dep, target, actual FROM ( SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep ) act JOIN ( SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep ) targ USING (Revenue_Dep) The output is If i use bot the query separtly i am getting output SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep; SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep; Can you please tell me where i am doing the mistake on Join query Quote Link to comment Share on other sites More sharing options...
Barand Posted August 2, 2023 Share Posted August 2, 2023 Before I do, tell me why you would write a php script using separate lines and indents to make it structure readable, but you write sql code on one line, disappearing off to the far distance? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 2, 2023 Author Share Posted August 2, 2023 I am using this script to display the data on my PHP page. So i wrote the my sql code on single line. On your previous suggession i changed the single line query and it works perfectly on my another page. SELECT act.category, target, actual FROM ( SELECT category, sum(quantity) as actual FROM tiv_data WHERE brand = 'XCMG' AND dealerid = '81019218' GROUP BY category ) act JOIN ( SELECT category, sum(total) as target FROM currentyeardata WHERE Dealer_ID = '81019218' GROUP BY category ) targ USING (category); Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 2, 2023 Author Share Posted August 2, 2023 Dear Barand, Pls help me Quote Link to comment Share on other sites More sharing options...
Barand Posted August 2, 2023 Share Posted August 2, 2023 3 hours ago, Senthilkumar said: But the output is not working. What does that mean? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 2, 2023 Author Share Posted August 2, 2023 Empty result Quote Link to comment Share on other sites More sharing options...
Barand Posted August 2, 2023 Share Posted August 2, 2023 Could be problems in your data. For example, if one of the tables had spaces at the end of the revenue_dep then it wouldn't match that in the other table. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 2, 2023 Author Share Posted August 2, 2023 Ok. I will check the table. Thanks for ur reply Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 3, 2023 Author Share Posted August 3, 2023 Dear Barand, I cleared the spaces on revenue_dep. Now i am getting output from the query. I have one more question. From my table revenue_data i am getting output of 5 rows. From my table revenuetarget i am getting output of 3 rows. With Joining of two tables the output is showing 3 rows only. Is it possible to display 5 rows joining both the tables and display 0 for Others and Workshop on column target. Pls guide me Quote Link to comment Share on other sites More sharing options...
Barand Posted August 3, 2023 Share Posted August 3, 2023 JOIN outputs matches from both tables. Only 3 have a match in the other table. If you want all data records even when there is no matching target record then you need ... FROM data table LEFT JOIN target table Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted August 3, 2023 Author Share Posted August 3, 2023 Thank you Quote Link to comment 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.