PostgreSQL select query doesn't work

Question

I have 3 tables:

CREATE table materials
 (id serial primary key not null,
 name varchar(50) not null,
 unit varchar(10) not null default 'шт',
 price decimal(12, 2) not null check (price>0));

CREATE table warehouses 
(id serial primary key not null,
 lastname varchar(25) not null);

CREATE table materials_in_warehouses
 (id_warehouses integer references warehouses(id) on update cascade on delete cascade,
 id_materials integer references materials(id),
 unit varchar(15) default 'шт',
 count integer not null CHECK (count>0),
 lastdate date not null, 
primary key (id_warehouses, id_materials);

And i need to select for each material : name; count of warehouses, where the material is present in an amount of > 100.

I tried to do something like this:

select materials.name, count(select * from materials_in_warehouses where 
materials.id = materials_in_warehouses.id_materials AND material_in_warehouses.count > 100) as sount from  materials;

But of course, this nonsense can't work.

Thank you in advance.

type materials.id is serial and materials_in_warehouses.id_materials is integer !? — ashkufaraz
– ashkufaraz, Commented Dec 21, 2015 at 18:22
@ashkufaraz serial is like auto_increment in mysql. It's an integer, but with autoincrement. It's still integer. — Alex
– Alex, Commented Dec 21, 2015 at 18:38

Community · Accepted Answer · 2017-05-23 12:08:59Z

1

Pretty straight forward.

SELECT m.name, count(miw.id_warehouses)
FROM materials m
LEFT JOIN materials_in_warehouses miw ON m.id=miw.id_materials AND miw."count">100
GROUP BY m.id, m.name

Your mistake might have just been the fact that you're using count as a column name, when it's an aggregate function. Use double quotes in PostgreSql for that: Escaping keyword-like column names in Postgres

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Dec 21, 2015 at 18:25

Alex

14.7k5 gold badges45 silver badges60 bronze badges

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

2 Comments

Peter Kozlovsky Over a year ago

works good, but why does not output materials, where count of warehouses = 0?

Alex Over a year ago

Are you testing the updated version? After I had answered yesterday, I realized that it would behave like that, so I eliminated the WHERE clause, and moved the condition into the LEFT JOIN.

ashkufaraz · Accepted Answer · 2015-12-21 18:25:07Z

0

Try like this

select materials.name, count(
select id_warehouses from materials_in_warehouses join materials 
on  materials.id = materials_in_warehouses.id_materials
where material_in_warehouses.count > 100
) as sount from  materials;

answered Dec 21, 2015 at 18:25

ashkufaraz

5,2997 gold badges54 silver badges85 bronze badges

Comments

Darwin von Corax · Accepted Answer · 2015-12-21 18:31:10Z

0

SELECT m.name, COUNT(w.id) AS locs
  FROM materials m, warehouses w, materials_in_warehouses mw
  WHERE m.id = mw.id_materials
    AND w.id = mw.id_warehouses
    AND mw.count > 100
  GROUP BY m.name;

If that works I'll come back and explain how I came up with it.

answered Dec 21, 2015 at 18:31

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Collectives™ on Stack Overflow

PostgreSQL select query doesn't work

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related