HAVING help

Databases
1

I have a table, with data like this:


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:

SELECT * FROM temp HAVING COUNT(number) > 1

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

2

I have tried everything I could get out of this article and I still can’t seem to get it to work.

Cheers,
Alex

3

I just fired the following sequence of queries:

CREATE TABLE temp (
  number VARCHAR(255),
  some_string VARCHAR(255),
  some_other_string VARCHAR(255)
);
 
INSERT INTO temp VALUES
(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');

SELECT
   number
FROM
  temp
GROUP BY
  number
HAVING
  COUNT(number) > 1

And it works as expected. The numbers I get are 1, 1004 and 1009

Are you sure you included GROUP BY in your query?

4

You should add GROUP BY to your query:

SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
  temp
GROUP BY
  number
HAVING
  count(number) > 1

:slight_smile: