-2

Good day to all of you, I would to raise this questions on my query.This issues occurs when using decrypt functionalities with group by functionalities.

SELECT decrypt(a.email_address, 'secret', 'aes') AS email_address, 
       decrypt(atx.account_description, 'secret', 'aes') AS account_description,
       a.account_id, a.account_type_id 
    FROM account_type AS atx 
    INNER JOIN accounts AS a ON atx.account_type_id=a.account_type_id 
    GROUP BY email_address

Table Fields Are :

Account_type <- table names
account_type_id - INT
account_description - BYTEA

Accounts <- table names
account_id - INT
account_type_id - INT
email_address - BYTEA

ERRORS shows this file

ERROR:  column "atx.account_description" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT decrypt(atx.account_description, 'secret', 'aes') AS ...

Did i do something wrong on the GROUP BY?Please advised.

But when you query something like this on MYSQL no problem encounter.As for the postgres is way to strict.I'm sorry i'm still newbie on postgres.This is the first time i'm using postgres on our new system.

Mary Daisy Sanchez
  • 947
  • 1
  • 12
  • 26
  • 2
    Please re-read the error again, it is telling you exactly what you are doing wrong. The `account_description` should either be in the group by or in a `min`/`max`/etc. – Wolph Sep 17 '14 at 07:14
  • 1
    What do you expect `GROUP BY` to do? – bereal Sep 17 '14 at 07:14
  • 1
    Why do you have the `group by` in there at all? You don't use any aggregates. What problem are you trying to solve? –  Sep 17 '14 at 07:40
  • @bereal id expect sorting of the email address via group by not declaring them each field, i'm sorry i'm not yet well verse on postgres. In mysql this is okay , no problem encounter.Postgres way to strict – Mary Daisy Sanchez Sep 17 '14 at 08:26
  • @boyee Sorting is done with `ORDER BY`, not with `GROUP BY`. `GROUP BY` may sort the results but it is not guaranteed. – Ihor Romanchenko Sep 17 '14 at 08:30
  • @boyee [GROUP BY](http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-GROUPBY) is not for sorting, it's mainly to collect aggregated values while grouping by others. – bereal Sep 17 '14 at 08:30
  • I'm sorry i mean grouping them, those postgres really put the all datafields in every fields like " Group BY email_address,atx.account_description,a.account_id,a.account_type_id,a,b,c,d,e" – Mary Daisy Sanchez Sep 17 '14 at 08:34
  • 1
    @boyee Why you want to group the fields? You do not use any aggregates in the query. – Ihor Romanchenko Sep 17 '14 at 08:35
  • @boyee BTW if you want to group by all fields (to remove duplicates) you can just `SELECT DISTINCT something, a, b, c FROM ...` – Ihor Romanchenko Sep 17 '14 at 08:37
  • i only want email_address on my group by but on the below @sathish declares all group by fields not on particular email_address – Mary Daisy Sanchez Sep 17 '14 at 08:41
  • Now i get it how the postgres behaves not like MYSQL thanks to all who help, i learn something new today! thanks guys , this is close – Mary Daisy Sanchez Sep 17 '14 at 08:57

1 Answers1

1

change your query like this

SELECT decrypt(a.email_address, 'secret', 'aes') AS email_address, 
decrypt(atx.account_description, 'secret', 'aes') AS account_description,
a.account_id, a.account_type_id 
FROM account_type AS atx 
INNER JOIN accounts AS a ON atx.account_type_id=a.account_type_id 
GROUP BY email_address,atx.account_description,a.account_id,a.account_type_id

You must give which columns are in select list with out agg. Functions columns

Sathish
  • 4,419
  • 4
  • 30
  • 59