Page 1 of 1

Help with HAVING?

Posted: Wed Jul 21, 2010 10:07 am
by alex.barylski
I have a table, with data like this:

Code: Select all

1	BODY ASSY	6892671
1	BUSHINGS	RO 08/1040
1	CASE ASSY	406-040-406-113
1	CYLINDER	2392-0847-005
1000	PT STUB SHAFT	3105421-01
10001	FREE TURBINE BEARING	0292808110
10002	AXIAL COMPRESSOR CASING	0292158084
10002A	AXIAL COMPRESSOR CASING	0292158050
10011	TURBINE SHAFT	0218270200
10013	NULL	NULL
10014	FLANGE	0242902490
10015	ARRL. INTER BEV GEAR	0292102760
10034	INTERMEDIATE PISTON	0292717250
10035	DRIVE GEAR	2218107600
10039	FUEL TUBE	0218157060
1004	LP BALANCING SHAFT ASSY	3108872-03
1004	LP ROTOR SHAFT	3115679-01
1004	LP SHAFT ASSY	3108872-01
10040	LINKING TUBE	0292757100
10041	OUTPUT SHAFT GEAR	0218110240
10042	ARR FRONT SUPPORT	0292108990
10044	DRIVE GEAR ASSEMBLY	0292109020
10045	POWER SHAFT	729210814
10046	P.T. BEARING ASSY	0292817260
10055	JUNCTION FLANGE	0292900810
10056	NULL	NULL
1006	PROP SHAFT	3038805
1008	6 & 7 BEARING TRANSFER TUBE	3105415-01
1009	HP Vane Ring Outer Support Housing	3106074-01
1009	OUTER SUPPORT HOUSING	3111397-01
I am trying to find all records where 'number' appears more than once, using a SQL query like:

[sql]SELECT * FROM temp HAVING COUNT(number) > 1[/sql]

However I am only getting a single record back for number '1' when with the above dataset I should also get:

1004
1009

Why am I not getting these other records?

Cheers,
Alex

Re: Help with HAVING?

Posted: Wed Jul 21, 2010 10:15 am
by pickle
Maybe try adding a GROUP BY clause?

Re: Help with HAVING?

Posted: Wed Jul 21, 2010 1:41 pm
by alex.barylski
Tried that, borrowed all ideas from:

http://www.petefreitag.com/item/169.cfm

Still no dice, not sure why :(

Re: Help with HAVING?

Posted: Wed Jul 21, 2010 3:36 pm
by Weirdan

Code: Select all

select number, count(*) 
from tableName 
group by number 
having count(*)>1

Re: Help with HAVING?

Posted: Fri Jul 23, 2010 2:21 am
by VladSun
WHERE is a filter clause applied to the rows before grouping, while HAVING is a filter clause applied to the result rows after GROUP BY. So, using HAVING without having a GROUP BY clause is senseless - it becomes a WHERE clause.