By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,479 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,479 IT Pros & Developers. It's quick & easy.

group by + count problem:

P: n/a
Hi,

I would like to count inspections and group them by customer (for those
where the repdate = #1/1/1900#)

Something like:
SELECT customerid, count() AS Inpections
FROM inspection
WHERE repdate = #1/1/1900#
GROUP BY customerid;

this works fine!
But now i would like the customer name instead of the ID
so i have to join in another table called Customer to find the CustomerName
with the right CustomerID
so i thought it would work like:

SELECT i.customerid, c.customername, count(i.repdate) AS Inpections
FROM inspection AS i, customer AS c
WHERE i.repdate = #1/1/1900#
and i.customerid = c.customerid
GROUP BY i.customerid, c.customername;

when i run this querry, the counts are not correct, (much to high) seems
like it also counts the iteration for the joined table or so.
anybody??

thanks a lot!
bjorn
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Try without Selecting customerid and group by customerid

Madhivanan

Nov 13 '05 #2

P: n/a
sorry, not working,

same result,
the cout is too high

"Madhivanan" <ma************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Try without Selecting customerid and group by customerid

Madhivanan

Nov 13 '05 #3

P: n/a
Can you post sample data with expected result?

Madhivanan

Nov 13 '05 #4

P: n/a
hi Madhivanan & others,

you can download an Occess sample db from www.SmartColourSensor.com/db1.mdb
(160Kb)
see querry 1 & 2 (and the tables)
they should give the same result, but don't

Reason is that in the customer table, 1 customer has multiple rows because
of branches.

thanks!
bjorn

"Madhivanan" <ma************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Can you post sample data with expected result?

Madhivanan

Nov 13 '05 #5

P: n/a
"b. hotting" <bh******@REMOVtHISwxs.nl> wrote in
news:d7**********@reader13.wxs.nl:
hi Madhivanan & others,

you can download an Occess sample db from
www.SmartColourSensor.com/db1.mdb (160Kb)
see querry 1 & 2 (and the tables)
they should give the same result, but don't

Reason is that in the customer table, 1 customer has multiple
rows because of branches.

thanks!
bjorn


You can fix this two ways, the right way is to split your
customer table into customers and customerbranches, or you can
replace the table in your query with a new query that does a
SELECT DISTINCT customerID, customername.
If you do it the first way, you now have a customerbranchID to
do even more sophisticated reporting.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.