I have three tables:
table1, table2, table3
I am trying to get a total count of rows from each table as well as sum of price column, so for example:
$r['count'] = total rows of all 3 tables combined;
$r['price'] = sum of all prices added together in all 3 tables combined;
Here is my query:
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table1` UNION
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table2` UNION
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table3`
When I run this query, I am getting:
count price
19 5609399
8 946000
4 0
Which in turn does not work when looped out in PHP as they are 3 separate values and I am only being returned "count = 19 and price = 5609399". They don't all come out totaled together as count or price.
Any help is greatly appreciated :)
解决方案SELECT COUNT(*) AS `count`, SUM(price) AS `price`
FROM
(
SELECT price from `table1` UNION ALL
SELECT price FROM `table2` UNION ALL
SELECT price FROM `table3`
) X
or generally to combine 3 pairs of values
select sum(`count`) `count`, sum(`price`) `price`
FROM
(
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table1` UNION ALL
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table2` UNION ALL
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table3`
) X
Generally, use UNION ALL when combining tables, not UNION because UNION removed duplicates, so if your count/sum were exactly
1, 100
1, 100
2, 200
A union query results in
1, 100 # duplicate collapsed
2, 200