mySQL select count, multiple tables

Question

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

RichardTheKiwi · Accepted Answer · 2011-04-07 07:11:00Z

4

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

answered Apr 7, 2011 at 7:11

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

manji · Accepted Answer · 2011-04-07 07:10:10Z

3

You can do this:

SELECT count(1) AS `count`, SUM(price) AS `price`
FROM (SELECT price FROM `table1`
      UNION ALL
      SELECT price FROM `table2`
      UNION ALL
      SELECT price FROM `table3`) AS `t`

answered Apr 7, 2011 at 7:10

manji

48k5 gold badges98 silver badges104 bronze badges

Comments

Devart · Accepted Answer · 2011-04-07 07:11:04Z

0

Try this one as pattern for your tables -

SELECT
  (SELECT COUNT(*) FROM table1) + (SELECT COUNT(*) FROM table2),
  (SELECT SUM(price) FROM table1) + (SELECT SUM(price) FROM table2);

answered Apr 7, 2011 at 7:11

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

mySQL select count, multiple tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related