Problem on sum of column in INNER JOIN query

[Senthilkumar]

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

[Barand]

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

 

[Senthilkumar]

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

[Barand]

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.

[Senthilkumar]

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

[Barand]

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

 

[Senthilkumar]

Dear Barand,

Thanks for your support. It is working now.

[Senthilkumar]

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

[Barand]

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?

[Senthilkumar]

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

 

[Senthilkumar]

Dear Barand,

Pls help me

[Barand]

3 hours ago, Senthilkumar said:

But the output is not working.

What does that mean?

[Senthilkumar]

Empty result

[Barand]

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.

[Senthilkumar]

Ok. I will check the table. Thanks for ur reply

[Senthilkumar]

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

[Barand]

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

[Senthilkumar]

Thank you