How to write a query to select from a PostgeSQL database?

Question

PostgreSQL

There is a phones table with the fields:
phone - varchar
users - int[]
There is a second table items
id serial
user_id int
status smallint (3 - not sold, 7 - sold, 5 - reserved)
You need to write a query that returns the number of sold and not sold phones for given phones sold items.
phone | sold | no_saled
------------------+-------+----------
7924445544 | 4 | 1
8985545444 | 1 | 2
In MySQL It's easy to do:

SELECT p.phone, 
SUM( i.status=7 ) as saled,
SUM( i.status=3 ) as no_saled
FROM items as i 
INNER JOIN phones as p 
ON i.user_id=p.users 
WHERE p.phone IN("7924445544", "8985545444") 
GROUP BY p.phone;

But in Postgres this construction does not work, help with good advice

Answer 1

If you only correct the query that works for you on the same scheme in mysql:

SELECT p.phone,
count(1) filter(where i.status=7 ) as saled,
count(1) filter(where i.status=3 ) as no_saled
FROM items as i
INNER JOIN phones as p
ON i.user_id=p.users
WHERE p.phone IN("7924445544", "8985545444")
GROUP BY p.phone;

If the schema is different, then the int[] syntax denotes an array of integers, such a standard data type. There is no such thing in mysql. Do you have an array of numbers?