Counting identical rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Counting identical rows

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-05-29 : 05:02:02

I've been chasing my tail a bit on this one so thought I'd ask for some help..
I want to select a user id from a table where that user has 10 identical rows in two columns in two tables
This is what I have so far;

SELECT COUNT(DISTINCT forecasts.forecast_id), forecasts.user_id
FROM
forecasts
JOIN
game_cards
ON
forecasts.home_forecast = game_cards.home_score
AND
forecasts.away_forecast = game_cards.away_score
WHERE
game_cards.card_id = @card_id
GROUP BY
forecasts.user_id

this gives me the count of identical rows and the user id but how do I get only those users with 10 identical rows?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-29 : 05:12:41

Would this help?

SELECT COUNT(forecasts.forecast_id), forecasts.user_id
FROM forecasts JOIN game_cards
ON forecasts.home_forecast = game_cards.home_score
AND forecasts.away_forecast = game_cards.away_score
WHERE game_cards.card_id = @card_id
GROUP BY forecasts.user_id
HAVING COUNT(forecasts.forecast_id) = 10

jackstow
Posting Yak Master

160 Posts

Posted - 2002-05-29 : 05:23:29

I think that might be the one - cheers

jackstow
Posting Yak Master

160 Posts

Posted - 2002-05-29 : 06:09:15

And it's just occured to me that I would need users with 20, 30, 40 etc identical records. Is there a syntax to check for a whole number? i.e. something like;

HAVING COUNT(forecasts.forecast_id)/10 = a whole number?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-29 : 06:15:05

HAVING COUNT(forecasts.forecast_id)%10 = 0

% (Modulo) provides the remainder of one number divided by another.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources