COUNT() with HAVING as subquery 1

[factotum]

Hello all,

I'm attempting to write a query that shows when there are multiple ProjectKeys assigned to one ProjectID. This needs to be fixed down the road, but first I must identify them. Some ProjectIDs don't have a ProjectKey at all, and that's okay for now, but I don't want them to show up in the query either (hence the IS NOT NULL). What I have below returns the records where there is a one-to-one map between the fields. I don't really understand why.

SELECT [ProjectID], [ProjectKey]
FROM

WHERE ProjectKey IS NOT NULL
AND EXISTS (SELECT [ProjectID], count([ProjectID]) FROM

GROUP BY [ProjectID] HAVING count([ProjectID]) > 1);

Any help would be greatly apprecitated, as I'm a newbie with SQL, and especially in an Access environment.

Thanks,

d.

 

[RickSpr]

That's a pretty advanced SQL statement for a "newbie"! I think you may be too humble.

Your problem is that the subquery is testing whether there are ANY project IDs that occur more than once. You want it to test whether the SPECIFIC project ID in the outer query exists more than once. In other words, you have to correlate the outer ProjectID with the inner one. Try this:

SELECT [ProjectID], [ProjectKey]
FROM

WHERE ProjectKey IS NOT NULL
AND EXISTS
(SELECT [ProjectID], count([ProjectID])
FROM

AS Table1
WHERE Table1.[ProjectID] = Table.[ProjectID]
GROUP BY [ProjectID]
HAVING count([ProjectID]) > 1);
Rick Sprague

 

[factotum]

Thanks Rick for taking the time to help me out. Your solution works, but as I tried to simplify the output, I came up with:

SELECT H.ProjectID, H.ProjectKey, count(ProjectID) as TotalMatches
FROM [table] AS H
WHERE ((H.ProjectKey) Is Not Null)
GROUP BY H.ProjectID, H.ProjectKey
HAVING (((COUNT(H.ProjectKey))>1));

What this one does instead, is groups the repeating rows into one row and adds the total. When I tried to rewrite yours to do the same, I ended up with two

HAVING ProjectID > 1

statements. I started yanking stuff out and then it dawned on me that I was running logic twice. Soon, I had removed everything in the middle. (I used ProjectKey > 1 condition instead, which makes no difference.)

Is your solution the only way to return each of the records (i.e. repeating the rows even though they are not unique)?

I realize that these two are accomplishing the same thing, and the one I rewrote seems to do so faster (Is that because it doesn't have to create the H1 table? (And that's virtual, right?) I've only been at SQL for a week or two in this capacity (versus very simple stuff). I'm not sure I truly grasp GROUP BY yet. Hence, newbie. Though I generally hold the bar high

 

[RickSpr]

Offhand, I can't think of another way to get all the rows, including duplicates. Except that you might be able to use an INNER JOIN instead of a subquery--but I expect it would execute the same way, which is to say, just as slowly.

I can't be certain, but it seems likely to me that eliminating the need to create a temporary results table (H1) is what makes your version run faster. BTW, it's only a "virtual" table if it's not actually created. Turning a virtual table into a real one is called "realizing" the table, and a query that realizes any intermediate table is likely to run slower.

GROUP BY and HAVING are hard to grasp well, and I still have trouble with them. I can never remember whether the HAVING or the WHERE occurs first, and sometimes it matters. I have even more trouble with them in the Access query grid (the so-called "totals" query). I usually switch to SQL mode to code them.

Glad you got what you needed. Rick Sprague