Hi Chris, Joerg, and everyone else following this discussion,
Joerg, you are correct; the best way to sum the tables is individually and
then add the sums together with program logic of some kind, such as might be
found in a script or application program or stored procedure.
I'm afraid I jumped in and gave correct but irrelevant information. I saw
that Chris's query lacked joining conditions so I explained why they were
needed and how to write them. Unfortunately, this was premature: I should
have thought about the basic problem more carefully first. Joerg, you are
absolutely right: if one table contains students and another contains
teachers, you don't count the number of people in the school by JOINING the
tables together. You count the people in each table separately and add the
two sums together. I don't know why that didn't come to me when I read
Chris's question but it didn't.
My apologies to all for wasting your time with an inappropriate solution.
I'll try not to do that again!
--
Rhino
----- Original Message -----
From: "Joerg Bruehe" <[EMAIL PROTECTED]>
To: "Chris Sansom" <[EMAIL PROTECTED]>
Cc: "Rhino" <[EMAIL PROTECTED]>; "MySQL List" <mysql@lists.mysql.com>
Sent: Wednesday, May 10, 2006 12:04 PM
Subject: Re: Sum of counts
Hi Chris, all,
Re-inserting Chris' original question:
| I want to get a total of entries from four tables which all match a
| particular id. The result for the id I'm testing (21) should be 233.
| In my naivety, I thought something like this would work:
|
| select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
| from table_a as a, table_b as b, table_c as c, table_d as d
| where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21
IMO, this is simply no task for a join,
probably not a task for any single SQL statement.
The easiest way is to have four separate "SELECT count(*) FROM table_?"
with the '?' replaced by 'a' .. 'd'.
More explanations below:
Chris Sansom wrote:
At 13:28 -0400 9/5/06, Rhino wrote:
The reason you are getting so many rows has nothing to do with the way
you are using the count(*) function and adding the different count()
results together. The problem is that you are doing your joins
incorrectly... In your case, I think you need to change the original
query to this:
select count(a.id) + count(b.id) + count(c.id) + count(d.id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = b.id
and b.id = c.id
and c.id = d.id
and a.id = 21
and b.id = 21
and c.id = 21
and d.id = 21
First, the transitive equality on the 4 "id" columns (first 3 conditions)
together with one restriction to 21 (say, on "a.id")
has no different effect than the 4 conditions "= 21",
in mathematical view 3 of these 7 conditions can be dropped.
(Not "any 3", but several different combinations.)
But that is not the cause of the problem - this is the join approach:
Remember that a join does a cartesian product, this is in no way helpful
to the solution of your task!
Let us construct a minimized example: Just two tables, each with three
rows, all having that magic value 21:
Table a Table b
id cnt id cnt
21 1 21 4
21 2 21 5
21 3 21 6
Doing a natural join on the "id" column will yield 9 rows:
a.id a.cnt b.id b.cnt
21 1 21 4
21 1 21 5
21 1 21 6
21 2 21 4
21 2 21 5
21 2 21 6
21 3 21 4
21 3 21 5
21 3 21 6
Summing "a.id" and "b.id" results in 18, where the correct value is 6.
Hi Rhino
Many thanks for the very full and frank response, but sadly it didn't
work. I do understand exactly what you said, and I even took it further,
adding in:
and a.id = c.id
and a.id = d.id
and b.id = d.id
...so that every table is thus related to every other one, but I'm
/still/ getting that damned eight and a half million instead of the 233 I
expect!
See above -
demanding all 4 columns to be equal to 21
is equivalent to demanding one is 21, and all are equal,
and also equivalent to some other combinations of conditions.
If you have a mathematical education, apply your knowledge of
"transitivity" to the problem.
I'm baffled by this, though the version I did with subqueries works very
nicely (and it's simple enough to do four separate queries and add them
together in the script for the older MySQL).
Frankly spoken: This is the way to go!
From your problem description, there seems to be no connection between the
tables that would warrant joining them.
For a very coarse analogy:
If you have separate tables for the teachers and the pupils of a school,
and want to know the number of all peoples going there daily,
you will not join these two tables,
you will rather count them individually and then add these two values.
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
