Jump to content

Problem on sum of column in INNER JOIN query


Senthilkumar
Go to solution Solved by Senthilkumar,

Recommended Posts

Dear Team,

 

I am having two different tables dbms.tiv_datadbms.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 

image.thumb.png.b7dfc76e7746a41bd23de459dc2f7d28.png

select Category, sum(Total) as Target from dbms.currentyeardata group by Category

The output is 

image.thumb.png.fa9f46e4ceac7098ad31f8522a852158.png

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 

image.thumb.png.0112f526c9084b2528a725d317dcdecf.png

 

Can any one please correct where i am doing mistake on this query

Link to comment
Share on other sites

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 |
+-----------+--------+--------+

 

Link to comment
Share on other sites

Dear Barand,

when i am running select * from query on tiv_data it returns total 11 rows 

image.thumb.png.10350b6c911d67d61877013b1469045d.png

 

When i am running select * from query on currentyear data it returns total 20 rows

image.thumb.png.01aa474b944d7715f989ac054b68ecdb.png

 

When i am running the inner join query as per your suggession i am getting total 90 row

image.thumb.png.5e37d21d692c683e721429e9e4f7adc1.png

 

Because of this the sum value is updating wrong.

image.thumb.png.be3b54a20fd278b7bd05710e7249c744.png

 

Can you please suggest me what to do for this problem

Link to comment
Share on other sites

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)"

Link to comment
Share on other sites

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);

 

Link to comment
Share on other sites

  • 2 weeks later...

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 

image.png.c549b5adffdf0edb2f12fe565bf598b6.png

 

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;

image.png.a9bb281905042ab437b6127b3f708fc5.png

 

SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep;

image.png.09655b33a03ba034e0c85fb76c70c003.png

 

Can you please tell me where i am doing the mistake on Join query

Link to comment
Share on other sites

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);

 

Link to comment
Share on other sites

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.

image.thumb.png.b5d8837a9da17616ba588bb30d12c1ac.png

 

From my table revenuetarget i am getting output of 3 rows.

image.thumb.png.d1af6efc19df5351bd72f376b995a93d.png

With Joining of two tables the output is showing 3 rows only.

image.thumb.png.c9fe018be2ce6aa65f1f242b18cecb5d.png

 

Is it possible to display 5 rows joining both the tables and display 0 for Others and Workshop on column target. 

Pls guide me

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.