On 12/03/2004 13:02 Gary Stainburn wrote:
Hi folks,If I understand you correctly, you've got something like
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)
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 |
+------------------------------+---------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
