Postgresql. select SUM value from arrays

Question

Condition:

There are two tables with arrays.

Note food.integer and price.food_id specified array.

CREATE TABLE food (
    id integer[] NOT NULL,
    name character varying(255),
);
INSERT INTO food VALUES ('{1}', 'Apple');
INSERT INTO food VALUES ('{1,1}', 'Orange');
INSERT INTO food VALUES ('{1,2}', 'banana');

and

CREATE TABLE  price (
    id bigint NOT NULL,
    food_id integer[],
    value double precision DEFAULT 0
);
INSERT INTO price VALUES (44, '{1}', 500);
INSERT INTO price VALUES (55, '{1,1}', 100);
INSERT INTO price VALUES (66, '{1,2}', 200);

Need to get the sum value of all the products from table food. Please help make a sql query.

ANSWER: {1} - Apple - 800 (500+100+200)

If you normalize your data model this becomes a very easy query. Storing foreign key references in an array is not a good idea. You should really consider fixing your data model — user330315
– user330315, Commented Feb 20, 2014 at 7:27
Yes, but I do not this base designed. If you know a query to this database, write or tell me where to look. — Theb
– Theb, Commented Feb 20, 2014 at 8:16
Why did I know you were going to tell us you didn't do it? The people creating wrong data models apparently never have to use them. I don't understand the array of ids in the food table. Does this mean "banana" has two different ids? And why does Orange have the same IDs as Apple? And what does it mean that "Orange" and "Banana" have two ids. What is the primary key of the food table? The model and the data do not make any sense to me. — user330315
– user330315, Commented Feb 20, 2014 at 8:19
You're right. This very bad model batabase. But I do not this base designed — Theb
– Theb, Commented Feb 20, 2014 at 8:35
Can you please answer my questions regarding your example data. I just don't understand how the two tables are related and what it means that a single food can have multiple "ID"s and what if means if they are the same and what it means if they are different — user330315
– user330315, Commented Feb 20, 2014 at 8:37

Tomas Greif · Accepted Answer · 2014-02-20 12:44:18Z

1

What about this:

select 
  name,
  sum(value)
from
  (select unnest(id) as food_id, name from food) food_cte
  join (select distinct id, unnest(food_id) as food_id, value from price) price_cte using (food_id)
group by
  name

It is difficult to understand your question, but this query at least returns 800 for Apple.

answered Feb 20, 2014 at 12:44

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

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

1 Comment

Theb Over a year ago

Yes this is it! I need only grouping by food.ID, not food.name :(

Balayesu Chilakalapudi · Accepted Answer · 2014-02-20 07:12:04Z

0

try the following command,

SELECT F.ID,F.NAME,SUM(P.VALUE) FROM FOOD F,PRICE P WHERE F.ID=P.FOOT_ID;

edited Feb 20, 2014 at 7:12

answered Feb 20, 2014 at 6:54

Balayesu Chilakalapudi

1,4023 gold badges20 silver badges43 bronze badges

5 Comments

Theb Over a year ago

Need for separateness for product: Apple - 800 (500+100+200); Orange - ...; Banana - ...;

Balayesu Chilakalapudi Over a year ago

there are no multiple prices for apple in your price table.

Theb Over a year ago

What sum price.value for apples? 800 (500+100+200) or other?

Balayesu Chilakalapudi Over a year ago

food.id=price.food_id is satisfied and your price table contains two or more records for a single id all the price values for an id will be added to sum().

Theb Over a year ago

But food.integer and price.food_id specified array. Сan make better use of design: unnest(F.ID) = any(P.FOOT_ID) ?

Collectives™ on Stack Overflow

Postgresql. select SUM value from arrays

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related