Need help with a Query

R

Rick A

I have a table that contains data similar to this

ID | EventDateID
12 100
12 110
12 120
13 110
13 120
13 130
14 110
....

Both fields make up the primary key and are both foreign keys.

I need to two counts
1) The number of times the ID shows up the first time
2) The number of times the ID shows up every time there after

For example: First time ID count would be equal to 3 - 12, 13, 14 (does
not matter what the EventDateID is)
"Second or more time" the ID count would be 4 too - 12, 12, 13, 13

I can count distinct to get the first time but the second or more time is
where's I'm struggling. Any thoughts would be greatly appreciated.

Thanks,

Rick
 
J

John Spencer

SELECT Sum(RepeatCount) as TotalRepeats
FROM
(SELECT ID, Count(ID)-1 as RepeatCount
FROM YourTable
GROUP BY ID) as NewTable
 
R

Rick A

works great. Thanks.

--
Rick Allison
John Spencer said:
SELECT Sum(RepeatCount) as TotalRepeats
FROM
(SELECT ID, Count(ID)-1 as RepeatCount
FROM YourTable
GROUP BY ID) as NewTable
Click to expand...
 
R

Rick A

John,

Can you help again?

Same key but with a day. I need to know the sum separated by day.

ID | EventID | Day
12 100 Fri
12 110 Fri
12 120 Fri
13 110 Fri
13 120 Fri
13 130 Fri
14 110 Fri
------------------------------------------------
12 100 Sat
12 110 Sat
12 120 Sat
13 110 Sat
13 120 Sat
13 130 Sat
14 110 Sat

Fri FirstCount = 3
Fri SecondCount = 4
Sat FirstCount = 3
Fri SecondCount = 4

Day is not part of the key.

Thanks,
 
J

John Spencer

Count the number of EventID's per day

SELECT [Day], Count(EventID) as Events
FROM
(SELECT Distinct Yourtable.Day, YourTable.EventID
FROM YourTable) as NewTable
GROUP BY [Day]

Count number of ID per day
SELECT [Day], Count(ID) as Events
FROM
(SELECT Distinct Yourtable.Day, YourTable.ID
FROM YourTable) as NewTable
GROUP BY [Day]
 
Top