>Hi,
>
>I try to use 2 COUNT's and a SUM in query from 2 tables, but am not able
>to get the results I want.
>
>The 2 tables are batches and testresults, and have the following fields
>and relation:
>
> batches: testresults:
> - batch_nr <---+ - id
> - date | - status
> - size +--- - batch_nr
>
>The contents is:
>
> batches:
> 136 1999-01-01 1
> 137 1999-01-01 24
> 138 1999-01-01 25
>
> testresultaten:
> 1 "Not OK" 137
> 2 "Not OK" 137
> 3 "Not OK" 137
>
>I use the following query:
>
> SELECT
>
> YEAR(batches.date) AS date_year,
> MONTH(batches.date) AS date_month,
> COUNT(batches.batch_nr) AS batch_count,
> SUM(batches.size) AS size_sum,
> COUNT(testresults.id) AS testresults_count
>
> FROM
>
> batches
> LEFT OUTER JOIN testresults ON
> (testresults.batch_nr=batches.batch_nr AND status="Not OK")
>
> GROUP BY
>
> date_year,
> date_month
>
> ORDER BY
>
> date_year,
> date_month
>
>This yields the following result:
>
> date_year date_month batch_count size_sum testresults_count
> 1999 1 5 98 3
>
>where I expect/want:
>
> date_year date_month batch_count size_sum testresults_count
> 1999 1 3 50 3
>
>When I don't use the COUNT on batch and SUM on size I get the following
>correct result:
>
> date_year date_month batch size testresults_count
> 1999 1 136 1 0
> 1999 1 137 24 3
> 1999 1 138 25 0
>
>It seems that the wrong size_count is calculated as follows:
>
> 3*24 + 1 + 25
>
>and the wrong batches_count as follows:
>
> 3 + 1 + 1
>
>So the question is: what do I have to do to get the desired result?
>
>Please copy your answer to my email address also, as I just subscribed
>to the list, and am not sure if I otherwise receive all answers.
Sir, I don't know what your desired result is.
If you selected all the columns from the two tables in your join,
without modifying them, you'd get this (copy & paste from an actual
run):
num run_date size id status batch_num
--- -------- ---- -- ------ ---------
136 1999-01-01 1 NULL NULL NULL
137 1999-01-01 24 1 Not OK 137
137 1999-01-01 24 2 Not OK 137
137 1999-01-01 24 3 Not OK 137
138 1999-01-01 25 NULL NULL NULL
(I changed the column names a little when I set this up.)
Note that the status = 'Not OK' condition has no effect when placed in the
ON clause. It needs to be an a WHERE clause to have any effect, but
it would be simpler in that case to skip the condition and just use
an INNER JOIN.
Since you're grouping on months and years, and since all the months
and years are the same, you are essentially not grouping at all. If
you do the counts and sum, you'll see where the numbers come from.
You can fix the problem with the first count by using DISTINCT
Count(DISTINCT num)
or you can just count results.batch_num. Or you can use an INNER
JOIN. It's not clear what exactly you're trying to accomplish, so
it's hard to say what will do it.
You're going to have to run a separate query to get the sum you want.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
