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, Dec 21 '15 at 18:22
@ashkufaraz serial is like auto_increment in mysql. It's an integer, but with autoincrement. It's still integer. — Alex, Dec 21 '15 at 18:38

score 1 · Accepted Answer · edited May 23 '17 at 12:08

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 '17 at 12:08

Community

1
1

answered Dec 21 '15 at 18:25

Alex

14,338
5
41
59

works good, but why does not output materials, where count of warehouses = 0? – Peter Kozlovsky Dec 22 '15 at 18:52
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. – Alex Dec 22 '15 at 22:22

score 0 · Answer 2 · answered Dec 21 '15 at 18:25

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 '15 at 18:25

ashkufaraz

5,179
6
51
82

score 0 · Answer 3 · answered Dec 21 '15 at 18:31

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.

PostgreSQL select query doesn't work

3 Answers3