Postgresql db

CREATE TABLE items ( id SERIAL, user_id INTEGER, status SMALLINT ); CREATE TABLE phones ( phone VARCHAR(12), users INTEGER[] );

Where items.status takes values ​​3, 5 or 7

Necessary for the list phone1, phone2 ... make a selection of the following form

phone1; COUNT (items.id) where status = 3; COUNT (items.id) where status = 7 phone2; COUNT (items.id) where status = 3; COUNT (items.id) where status = 7 ...

My version breeds doubles and then summarizes

 SELECT phones.phone as phone, COUNT(it1.id) as saled_count, COUNT(it2.id) as not_saled_count FROM phones INNER JOIN items it1 ON phones.users @> ARRAY[it1.user_id]::int[] INNER JOIN items it2 ON phones.users @> ARRAY[it2.user_id]::int[] WHERE phones.phone IN ('phone1', 'phone2') AND it1.status = 7 AND it2.status = 3 GROUP BY phones.phone;

Result accordingly

 phone1 5 5 phone2 6 6 ...

Instead

 phone1 3 2 phone2 5 1 ...

Well, the question itself: how to make a request?

    1 answer 1

    If I understand the problem correctly

     select p.phone, sum(case i.status when 3 then 1 else 0 end) as status3, sum(case i.status when 5 then 1 else 0 end) as status5, sum(case i.status when 7 then 1 else 0 end) as status7 from phones as p inner join items as i on i.user_id = any(p.users) where p.phone in ('phone1', 'phone2') group by p.phone;
    • Brilliant. There are no words. - Kamo Petrosyan
    • I just recently came across a similar task :) If the answer helped you, mark it with a solution. - Sergey Gornostaev
    • 2
      CASE is optional, you can simply SUM (i.status = 3) AS status3 . - Akina