Count while excluding entries...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a table that essentially looks like this:
ID001 Category 1
ID002 Category 3
ID002 Category 4
ID003 Category 3

and I want to do a count of all entries that are of Category 3. If only it
were that easy...The problem is that I have to NOT count any Category 3 entry
that also has a Category 4 entry. Basically the Category 4 status trumps the
Category 3 status. So in the example above my Category 3 count would be 1
(ID003).

Any idea how to do this, preferably with one query?

As always thanks very much. You guys are the best!
Brett
 
Hi there

See if this query works:

SELECT COUNT (*)
FROM [Table_Name]
WHERE ID IN (SELECT ID FROM [Table_Name] WHERE Category = 'Category 3')
AND ID NOT IN (SELECT ID FROM [Table_Name] WHERE Category = 'Category 4');

Lucas
 
Try this --
SELECT Brett_D.ID, Brett_D_1.Category, Count(Brett_D_1.Category) AS
CountOfCategory1
FROM Brett_D LEFT JOIN Brett_D AS Brett_D_1 ON (Brett_D.Category =
Brett_D_1.Category) AND (Brett_D.ID = Brett_D_1.ID)
WHERE (((Brett_D_1.Category)<>"Category 4"))
GROUP BY Brett_D.ID, Brett_D_1.Category;
 
Back
Top