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.
Thanks in advance,
Wouter.
P.S. I am using MySQL version 3.22.32
--
Wouter Siteur
Business Process Controller
Chess embedded Technology, Nieuwe Gracht 78, 2011 NC Haarlem
tel: +31 23 5 149 146, fax: +31 23 5 149 199
http://www.chess.nl
---------------------------------------------------------------------
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
