Counting

[haydenal]

I have a query that includes [NAME], [INCIDENT] and [CLASS]. Can anyone show me an example of doing a query that would give me a count of unique incidents by unique name? For example, my data might look like this:

NAME INCIDENT CLASS
TIM 1 x
TIM 1 y
TIM 1 z
TIM 2 x
BILL 3 a
TED 1 b

I'm trying to find TIM = 2, BILL = 1 and TED = 1

 

[HiTechCoach]

I have a query that includes [NAME], [INCIDENT] and [CLASS]. Can anyone show me an example of doing a query that would give me a count of unique incidents by unique name? For example, my data might look like this:

NAME INCIDENT CLASS
TIM 1 x
TIM 1 y
TIM 1 z
TIM 2 x
BILL 3 a
TED 1 b

I'm trying to find TIM = 2, BILL = 1 and TED = 1

I would think Tim should = 4 not 2. Which is correct?


You could use a totaling query that groupo n [NAME] (note that NAME is a reserved word and should not be used for user named objects). I would count the primary key field

 

[pbaldy]

I think it is 2 Boyd, as that's the number of unique incidents (1 and 2). I do agree about the reserved words. I'm not the best with SQL, but try something like this:

SELECT Alias.NAME, Count(Alias.NAME) AS HowMany
FROM
(SELECT NAME, INCIDENT
FROM TableName
GROUP BY NAME, INCIDENT) AS Alias
GROUP BY Alias.NAME

 

[haydenal]

Tim should equal 2 (1 and 2).

I'll give the SQL and [NAME] suggestions a shot on Friday. Thanks gents!

 

[haydenal]

Paul,

That did the trick, thanks much!

 

[pbaldy]

No problem; glad it worked. It looked a little clunky, but I thought it should work.