There are tables

  1. types with fields name, keyid
  2. phones with fields keyid, typeid, pname, status

field status = 0 and 1 (0 - old, 1 - new)

How do I query the data in the form

 Тип // Новый // Старый // Количество

For example:

 Тип Новые Старые Количество Смартфоны 5 6 11 Обычные телефоны 2 3 5
  • If the records "new" and "old" are not more than one of each type (for one keyid), then make two left join phones to the types, and directly in the conditions of ON, specify the connections for one status = 0, and 1 for the other. - Mike
  • Record is always one, the status can change - Natalya Sergeevna
  • can a small example? I didn’t really understand the idea - Natalia Sergeevna
  • If the record is one, then that in the columns "new" and "old" - Mike
  • No, you did not understand. There are a lot of records in general, a lot. I mean a record with a unique keyid one. At such a record, only the status field changes - Natalya Sergeevna

1 answer 1

Try this:

 with types(name, keyid) as ( select 'AAA', 1 from dual union all select 'BBB', 2 from dual union all select 'CCC', 3 from dual ), phones(keyid, typeid, pname, status) as ( select 1, 0, 'Текст', 0 from dual union all select 1, 0, 'Текст', 0 from dual union all select 1, 0, 'Текст', 1 from dual union all select 1, 0, 'Текст', 0 from dual union all select 2, 0, 'Текст', 1 from dual union all select 2, 0, 'Текст', 0 from dual union all select 3, 0, 'Текст', 0 from dual union all select 3, 0, 'Текст', 1 from dual union all select 3, 0, 'Текст', 0 from dual union all select 3, 0, 'Текст', 1 from dual ) -- Ваш запрос: select name, sum(old), sum(new), count(status) from (select t.name, decode(p.status, 0, 1, 0) old, decode(p.status, 1, 1, 0) new, p.keyid, p.status from types t join phones p on p.keyid = t.keyid) group by name order by 1