Help with HAVING?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Help with HAVING?

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Help with HAVING?

Post by pickle »

Maybe try adding a GROUP BY clause?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Help with HAVING?

Post by alex.barylski »

Tried that, borrowed all ideas from:

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

Still no dice, not sure why :(
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Help with HAVING?

Post by Weirdan »

Code: Select all

select number, count(*) 
from tableName 
group by number 
having count(*)>1
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with HAVING?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply