CODEWITHSUNDEEP
postgresql
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, syntax in query issue

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.

Upvotes: 1

Views: 1298

Answers (1)

user359040
user359040

Reputation:

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.

Upvotes: 3

Related Questions