Counting records - 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

General SQL Server Forums

New to SQL Server Programming

Counting records

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-18 : 10:16:59

Hello all.

I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:

select * from U_DATAFILE
where count(group by VIEW_ACTION)>1

Thanks all :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 10:21:38

[code]SELECT u.*
FROM U_DATAFILE u
INNER JOIN (
SELECT VIEW_ACTION
FROM U_DATAFILE
GROUP BY VIEW_ACTION
HAVING COUNT(*) > 1
) z ON z.VIEW_ACTION = u.VIEW_ACTION[/code]

Peter Larsson
Helsingborg, Sweden

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-18 : 10:25:48

Hi thanks very much. That code does work. However i'm sure there is an easier way to do it wothout using a join?

Anyone?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 10:31:31

[code]SELECT u.*
FROM U_DATAFILE u
WHERE u.VIEW_ACTION IN (
SELECT b.VIEW_ACTION
FROM U_DATAFILE b
GROUP BY b.VIEW_ACTION
HAVING COUNT(*) > 1
)[/code]

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 10:33:16

well


SELECT		u.*
FROM		U_DATAFILE u
where VIEW_ACTION in	(
			SELECT		VIEW_ACTION
			FROM		U_DATAFILE
			GROUP BY	VIEW_ACTION
			HAVING		COUNT(*) > 1
			) z

It's the same code - no join (ish) but....
Don't think you'll find anything better.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 10:33:33

I am not sure of this one

SELECT		u.*
FROM		U_DATAFILE u
WHERE		EXISTS	(
				SELECT		b.VIEW_ACTION
				FROM		U_DATAFILE b
				WHERE		b.VIEW_ACTION = u.VIEW_ACTION
				GROUP BY	b.VIEW_ACTION
				HAVING		COUNT(*) > 1
			)

or this one

SELECT		u.*
FROM		U_DATAFILE u
WHERE		EXISTS	(
				SELECT		b.VIEW_ACTION
				FROM		U_DATAFILE b
				GROUP BY	b.VIEW_ACTION
				HAVING		COUNT(*) > 1
						AND b.VIEW_ACTION = u.VIEW_ACTION
			)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 10:37:22

quote:
Originally posted by GavinD1977

Hi thanks very much. That code does work. However i'm sure there is an easier way to do it wothout using a join?

Anyone?

Is this a hidden frustrated "help me, I am stuck with homework and your solution isn't among the key answers" question?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 10:48:54

That last one has to be the worst of the lot.
but


SELECT		u.*
FROM		U_DATAFILE u
WHERE		(	SELECT		count(*)
			FROM		U_DATAFILE b
			WHERE		b.VIEW_ACTION = u.VIEW_ACTION
		) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-18 : 10:52:12

Hi. Nope this isnt a 'help me i'm stuck with homework'. I'm a 29 years old developer who is just starting out with SQL Server.

I did thank you for you post, and my reply wasnt meant to be derogatory in anyway, so i apologise if you took it that way.

Anyway I have a bit of code which i thought was simliar and wouldnt have needed a join. The code is:

SELECT EMPLOY_REF, FROMDATE FROM ABSENCE WHERE ABS_REASON LIKE '%pub%'GROUP BY EMPLOY_REF, FROMDATE HAVING COUNT(*) > 1

Thanks again.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 11:06:06

That won't give the row just the duplicate column values.
It's the full row that needs the join otherwise you could just use the subquery
SELECT VIEW_ACTION
FROM U_DATAFILE
GROUP BY VIEW_ACTION
HAVING COUNT(*) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-18 : 11:08:23

Hi. Thanks nr..........much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources