Thread: where not unique
Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. Secondly, when I run the full query I get: usedcars=# select stock_number, registration from stock usedcars-# where registration in usedcars-# (select registration, count(registration) as count from stock group by registration); ERROR: Subselect has too many fields usedcars=# which is obviously because of the count field. Can anyone tell me where I'm going wroing with these count fields? (I've tried renaming the field to regcount in case it was a reserved word problem). -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn wrote: >Hi folks, > >I've got a table which contains amongst other things a stock number and a >registration number. I need to print out a list of stock number and reg >number where reg number is not unique (cherished number plate transfer not >completed). I've tried variations of a theme based on > >select stock_number, registration from stock where registration in > (select registration, count(registration) as count > from stock where count > 1 group by registration); > >but I have two problems. Firstly with the sub-select I get: > >usedcars=# select registration, count(registration) as count from stock where >count > 1 group by registration; >ERROR: Attribute 'count' not found >usedcars=# > >although if I miss out the where clause I get the expected results. > I think that this is not the best way, but : select * from (select registration, count(registration) as counter from stock group by registration) where counter > 1; > >Secondly, when I run the full query I get: > >usedcars=# select stock_number, registration from stock >usedcars-# where registration in >usedcars-# (select registration, count(registration) as count from stock group >by registration); >ERROR: Subselect has too many fields >usedcars=# > > This is because the subselect is returning 2 columns but "in clause" is expecting just one column. Try to use "exists" instead of "in", ok ? >which is obviously because of the count field. > >Can anyone tell me where I'm going wroing with these count fields? >(I've tried renaming the field to regcount in case it was a reserved word >problem). > > I hope this helps you.
On 12/03/2004 13:02 Gary Stainburn wrote: > Hi folks, > > I've got a table which contains amongst other things a stock number and a > > registration number. I need to print out a list of stock number and reg > number where reg number is not unique (cherished number plate transfer > not > completed). I've tried variations of a theme based on > > select stock_number, registration from stock where registration in > (select registration, count(registration) as count > from stock where count > 1 group by registration); > > but I have two problems. Firstly with the sub-select I get: > > usedcars=# select registration, count(registration) as count from stock > where > count > 1 group by registration; > ERROR: Attribute 'count' not found > usedcars=# > > although if I miss out the where clause I get the expected results. > > Secondly, when I run the full query I get: > > usedcars=# select stock_number, registration from stock > usedcars-# where registration in > usedcars-# (select registration, count(registration) as count from stock > group > by registration); > ERROR: Subselect has too many fields > usedcars=# > > which is obviously because of the count field. > > Can anyone tell me where I'm going wroing with these count fields? > (I've tried renaming the field to regcount in case it was a reserved word > > problem) If I understand you correctly, you've got something like mytable stockno regno -------------- SN1 REG1 SN2 REG2 SN3 REG3 SN4 REG2 and you want to list REG2. Something like select regno from mytable group by regno having count(stockno) > 1; might do it. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
You're right, Reinoud. Sorry for my mystake... Reinoud van Leeuwen wrote: >On Fri, Mar 12, 2004 at 10:54:25AM -0300, Daniel Henrique Alves Lima wrote: > > >>Gary Stainburn wrote: >> >> >> >>>Hi folks, >>> >>>I've got a table which contains amongst other things a stock number and a >>>registration number. I need to print out a list of stock number and reg >>>number where reg number is not unique (cherished number plate transfer not >>>completed). I've tried variations of a theme based on >>> >>>select stock_number, registration from stock where registration in >>> (select registration, count(registration) as count >>> from stock where count > 1 group by registration); >>> >>>but I have two problems. Firstly with the sub-select I get: >>> >>>usedcars=# select registration, count(registration) as count from stock where >>>count > 1 group by registration; >>>ERROR: Attribute 'count' not found >>>usedcars=# >>> >>>although if I miss out the where clause I get the expected results. >>> >>> >>> >> I think that this is not the best way, but : >> >>select * from (select registration, count(registration) as counter from >>stock group by registration) >>where counter > 1; >> >> > >Isn't that the same as > >select registration >from stock >group by registration >having count (registration) > 1; > > > >