Jacque Scott wrote:
My program, NCR (Non-Conformity Report), keeps track of problems with
items that are received from vendors. I am creating a report where the
user can retrieve a list of vendors that have had a NCR written against
them a certain number of times. For example, if the user wants to see
what vendors have had more than 2 NCRs written against them. I need something like this, but it doesn't work. It gives me an error
saying invalid use of group function. So I put Count(Subs.Vendor) > 2
into a HAVING clause and it return only one vendor. I know there are
more.
SELECT Subs.Vendor
FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo WHERE ((tblNCRLog.CurrentDate > '2002/10/1') AND Count(Subs.Vendor) >
2);
Does someone have any suggestions?
Jacque
You say you use HAVING, but it's not in your example query. Perhaps that's an oversight. But you're also missing a GROUP BY. Your join gets you one row per NCR per vendor. You need to group by vendor, then check the count with HAVING. I think you need something like:
SELECT Subs.Vendor, COUNT(*) AS NCRs FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate > '2002/10/1' GROUP BY Subs.Vendor HAVING NCRs > 2
That would give the vendors and the counts. If you really don't want to see the counts, so long as they're greater than 2, something like this should do:
SELECT Subs.Vendor FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate > '2002/10/1' GROUP BY Subs.Vendor HAVING COUNT(*) > 2
Hope that helps.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
