Sergey E. Koposov wrote:
Hi All!
I experienced the strange error when running a simple query.
1) I have the following tables :
wsdb=# \d slit_data
Table "public.slit_data"
Column | Type | Modifiers
--------+------------------+-----------
chip | smallint |
tile | smallint |
slit | smallint |
id | integer |
cx | double precision |
....
wsdb=# \d gems_spec
Table "public.gems_spec"
Column | Type | Modifiers
-----------+-----------------------+-----------
tile | smallint |
chip | smallint |
id | integer |
priority | smallint |
....
2) When I run the query like this
wsdb# SELECT id,slit,tile,chip FROM slit_data LEFT JOIN gems_spec
USING(id,tile,chip) WHERE cx>0.5 ORDER BY priority,id;
it runs smoothly, but when I run the same query with aggregate, I get the
error:
wsdb=# SELECT count(*) FROM slit_data LEFT JOIN gems_spec
USING(id,tile,chip) WHERE cx>0.5 ORDER BY priority,id;
ERROR: column "gems_spec.priority" must appear in the GROUP BY clause or be
used in an aggregate function
Is that normal? I really do not see the reason for the error. I even don't
use the "GROUP BY" clause. Am I wrong ?
Remove the order by and you should be fine:
SELECT count(*) FROM slit_data LEFT JOIN gems_spec
USING(id,tile,chip) WHERE cx>0.5;
When you use any aggregate function - count, avg, sum, etc if you are
ordering your results, you need to group your results:
SELECT count(*) FROM slit_data LEFT JOIN gems_spec USING(id,tile,chip)
WHERE cx>0.5 GROUP BY priority, id, ..... ORDER BY priority,id;
You have to list all columns because that's what you're counting - *
Alternatively:
select count(chip) FROM slit_data LEFT JOIN gems_spec
USING(id,tile,chip) WHERE cx>0.5 GROUP BY chip, priority, id ORDER BY
priority, id;
and you only need to group by chip (because that's all your counting).
You have to group by priority and id because they are in your order
results (I think postgres forces you to include them in the group by
because they are in the order by - try just grouping by chip and see
what happens anyway).
--
Postgresql & php tutorials
http://www.designmagick.com/
