PostgreSQL, syntax in query issue

Question

I already get good answer on my question here

So If I create tables:

CREATE TABLE IF NOT EXISTS usedfood 
(food_code int, name text, qty int, meas text);

INSERT INTO usedfood (food_code, name, qty, meas) 
VALUES  (10,  'spaghetti', 3, 'pcs'), 
        (156, 'mayonnaise', 2, 'pcs'), 
        (173, 'ketchup', 1, 'pcs'), 
        (172, 'bolognese sauce', 2, 'pcs'), 
        (173, 'ketchup', 1, 'pcs'), 
        (10,  'spaghetti', 2, 'pcs'), 
        (156, 'mayonnaise', 1, 'pcs');

CREATE TABLE IF NOT EXISTS ingredients 
(food_code int, ingr_code int, name text, qty decimal(11,3), meas text);

INSERT INTO ingredients (food_code, ingr_code, name, qty, meas) 
VALUES  (10,  1256, 'spaghetti rinf', 75, 'gramm'), 
        (156, 1144, 'salt', 0.3, 'gramm'), 
        (10,  1144, 'salt', 0.5, 'gramm'), 
        (156, 1140, 'fresh egg', 50, 'gramm'), 
        (172, 1138, 'tomato', 80, 'gramm'), 
        (156, 1139, 'mustard', 5, 'gramm'), 
        (172, 1136, 'clove', 1, 'gramm'), 
        (156, 1258, 'oil', 120, 'gramm'), 
        (172, 1135, 'laurel', 0.4, 'gramm'), 
        (10,  1258, 'oil', 0.4, 'gramm'), 
        (172, 1130, 'corned beef', 40, 'gramm');

and execute this query from my PostgreSQL:

SELECT SUM(f.qty) used_times, 
COALESCE(i.ingr_code, f.food_code) code, 
COALESCE(i.name, f.name) name, 
SUM(COALESCE(i.qty, 1) * f.qty) qty, 
COALESCE(i.meas, f.meas) meas 
FROM usedfood f LEFT JOIN ingredients i 
ON f.food_code = i.food_code 
GROUP BY i.ingr_code, i.name

... I still cannot get expected result like this one on SQL fiddle

I get such errors:

ERROR: syntax error at or near "name" LINE 1: ...code, f.food_code) code, COALESCE(i.name, f.name) name, SUM(...

ERROR: column "f.food_code" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...LECT SUM(f.qty) used_times, COALESCE(i.ingr_code, f.food_cod...

ERROR: column "f.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...(i.ingr_code, f.food_code) code, COALESCE(i.name, f.name) in...

ERROR: column "i.meas" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...me, SUM(COALESCE(i.qty, 1) * f.qty) qty, COALESCE(i.meas, f....

ERROR: column "f.meas" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...COALESCE(i.qty, 1) * f.qty) qty, COALESCE(i.meas, f.meas) me...

It is most likely that this code isn't fully compatible with postgreSQL so please if someone can fix this code from query to be proper for run from PostgreSQL system.

What's unclear about "column "f.meas" must appear in the GROUP BY clause" — user330315
– user330315, Commented Jun 12, 2013 at 12:22
This is clear, but when I do as "error report" suggest I get incorrect result. — Wine Too
– Wine Too, Commented Jun 12, 2013 at 12:29
"incorrect results" is way too vague. Show us what the output is you expect (based on your sample data) — user330315
– user330315, Commented Jun 12, 2013 at 12:30
Of course I give it already, calculated "by hand", just follow a link in my question. But Mark solves this issue already. Anyway, thanks for trying to help. — Wine Too
– Wine Too, Commented Jun 12, 2013 at 12:48

user359040user359040 · Accepted Answer · 2013-06-12 12:24:03Z

3

The problems are mostly due to the fact that PostgreSQL (like most other RDBMSs, but unlike MySQL) requires selected items to be either grouped or aggregated in queries that are grouped/aggregated. Try:

SELECT SUM(f.qty) used_times,
       COALESCE(i.ingr_code, max(f.food_code)) code,
       COALESCE(i.name, max(f.name)) "name",
       SUM(COALESCE(i.qty, 1) * f.qty) qty,
       COALESCE(max(i.meas), max(f.meas)) meas
  FROM usedfood f LEFT JOIN ingredients i
    ON f.food_code = i.food_code
 GROUP BY i.ingr_code, i.name

SQLFiddle here.

answered Jun 12, 2013 at 12:24

user359040

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

That should be "all SQL based databases require selected items to be grouped or aggregated because it's defined by the SQL standard that way. MySQL violates that requirements and simply returns random results"

Wine Too Over a year ago

Yes, that's it. I uses that from C and now get correct results without errors and warnings. Thank You.

Craig Ringer Over a year ago

IIRC the SQL spec does expect the DB to recognise when you're grouping by the primary key of a table and figure out that other columns of the same table are implictly grouped; certainly newer versions of PostgreSQL do just that.

Collectives™ on Stack Overflow

PostgreSQL, syntax in query issue

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related