CODEWITHSUNDEEP
mysqljoin
Share Knowledge
Share Knowledge

Reputation: 297

Getting the sum of several columns from two tables result is not correct

I'am trying to get the sum of the two columns from different tables, however i have found great posts on stack. some of them helped me out. but i still can't solve this problem out..

This query somehow down below returns incorrect total of the sum of the coulmns, ( rate Coulmn - materialprice Column ) 

mysql> Tbl  as_servicetickets;
+----------+----------+
|ticket_id |  rate    |
+----------+----------+
|       11 |   250.00 |
|       11 |   300.00 |
|       11 |   400.00 |
|        9 |   300.00 |
|        9 |   300.00 |
|        9 |  1500.00 |
|        9 |   250.00 |
+----------+----------+
total is 2 350.00

mysql> Tbl as_ticketmaterials;
+----------+---------------+
|ticket_id | materialprice |
+----------+---------------+
|       11 |           100 |
|        9 |            20 |
|        9 |            50 |
+----------+---------------+
total is 70.00

query---------------------////

SELECT SUM(`as_servicetickets`.`rate`) AS `sercnt`,  SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
FROM  `as_servicetickets`, `as_ticketmaterials`
WHERE `as_servicetickets`.`ticket_id` = 9
AND   `as_ticketmaterials`.`ticket_id` = 9
GROUP BY `as_servicetickets`.`ticket_id`, `as_ticketmaterials`.`ticket_id

result ------------------///// this is not correct
+---------+--------+
| sercnt  | sercnt |
+---------+--------+
| 4700.00 |    280 |
+---------+--------+

Upvotes: 1

Views: 45

Answers (3)

Shailesh Katarmal
Shailesh Katarmal

Reputation: 2785

select sum(a.rate) as sercnt, sum(b.materialprice) as sercnt from
as_servicetickets a inner join as_ticketmaterials b on 
a.ticket_id = b.ticket_id  where a.ticket_id = 9

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

This is not the correct way to achieve the desired result. Try this rather:-

SELECT (SELECT SUM(`as_servicetickets`.`rate`) AS `sercnt`
        FROM `as_servicetickets`
        WHERE `as_servicetickets`.`ticket_id` = 9),
       (SELECT  SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
        FROM  `as_ticketmaterials`
        WHERE `as_ticketmaterials`.`ticket_id` = 9);

Upvotes: 1

SMA
SMA

Reputation: 37023

Try using explicit join as implicit joins are discouraged (You where condition has issue)

SELECT `as_servicetickets`.`ticket_id`, SUM(`as_servicetickets`.`rate`) AS `sercnt`,  SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
FROM  `as_servicetickets` INNER JOIN `as_ticketmaterials`
ON `as_servicetickets`.`ticket_id` = `as_ticketmaterials`.`ticket_id` 
WHERE `as_servicetickets`.`ticket_id` = 9
GROUP BY `as_servicetickets`.`ticket_id`

Upvotes: 1

Related Questions