Запрос который по каждому менеджеру показывает сумму продаж

Question

У меня есть несколько таблиц в БД PostgreSQL, не могу правильно найти сумму продаж по каждому менеджеру. Нужен запрос который по каждому менеджеру показывает:(id, name, salary, plan, сумму продаж – total, которую совершил менеджер). Нужно отсортировать менеджеров по total (по убыванию). И если у менеджера нет продаж, то в столбце total должно быть 0.

CREATE TABLE customers
(
   id          BIGSERIAL           PRIMARY KEY,
   name        TEXT                NOT NULL,
   phone       TEXT                NOT NULL UNIQUE,
   active      BOOLEAN             NOT NULL DEFAULT TRUE,
   created     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE managers
(
   id          BIGSERIAL           PRIMARY KEY,
   name        TEXT                NOT NULL,
   salary      INTEGER             NOT NULL CHECK ( salary > 0 ),
   plan        INTEGER             NOT NULL CHECK ( salary > 0 ),
   boss_id     BIGINT              REFERENCES managers,
   department  TEXT,
   active      BOOLEAN             NOT NULL DEFAULT TRUE,
   created     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products
(
   id          SERIAL           PRIMARY KEY,
   name        TEXT                NOT NULL,
   price       INTEGER             NOT NULL 0 CHECK ( price > 0 ),
   qty         INTEGER             NOT NULL,
   active      BOOLEAN             NOT NULL DEFAULT TRUE,
   created     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sales 
(
   id          BIGSERIAL       PRIMARY KEY,
   manager_id  BIGINT          NOT NULL REFERENCES users,
   customer_id BIGINT          NOT NULL,
   created     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sale_positions
(
   id          BIGSERIAL           PRIMARY KEY,
   sale_id     BIGINT              NOT NULL REFERENCES sales,
   product_id  BIGINT              NOT NULL REFERENCES products,
   price       INTEGER             NOT NULL CHECK ( price >= 0 ),
   qty         INTEGER             NOT NULL DEFAULT 1 CHECK ( qty >= 0),
   created     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT  INTO  managers(name, salary, plan, boss_id, deparmt)
VALUES  ('Vasya', 100, 0, NULL, NULL),
        ('Petya', 80, 80, 1 ,'boys'),
        ('Vanya', 60, 60, 2 ,'boys'),
        ('Dasha', 90, 90, 1 ,'girls'),
        ('Sas ha', 70, 70, 4,'girls'),
        ('Masha',  50, 50, 5,'girls');

INSERT INTO sales (manager_id, customre_id) 
VALUES (1, DEFAULT),
       (2, DEFAULT),
       (3, DEFAULT),
       (4, 1),
       (4, 1),
       (5, DEFAULT),
       (5, DEFAULT);

INSERT INTO sale_positions(sale_id,product_id, name, qty, price)
VALUES
( 1, 1, 'Pizza', 5, 200),

( 1, 2, 'Burger', 5, 200),

(2, 3, 'Free', 10, 120),

(3, 3, 'Free', 10, 120),

(4, 6, 'Coffee', 20, 150),

( 5, 6, 'Coffee', 20, 150),

(6, 6, 'Coffee', 20, 150),

(7, 5, 'Cota', 10, 100);

У меня получается так:

select s.manager_id as id, m.name, m.salary * 1000 salary, m.plan * 1000 plan, ss.total from sales s
Join managers m On m.id=s.manager_id
Join (select sp.sale_id, sum(sp.price * sp.qty) total from sale_positions sp 
GROUP BY sp.sale_id
) ss on s.id=ss.sale_id
Order By total desc

id	name	salary	plan	total
4	Dasha	90000	90000	3000
4	Dasha	90000	90000	3000
5	Sasha	70000	70000	3000
1	Vasya	100000	0	2000
2	Petya	80000	80000	1200
3	Vanya	60000	60000	1200
5	Sasha	70000	70000	1000

А должно быть так:

id	name	salary	plan	total
4	Dasha	90000	90000	6000
5	Sasha	70000	70000	4000
1	Vasya	100000	0	2000
2	Petya	80000	80000	1200
3	Vanya	60000	60000	1200
6	Masha	50000	50000	0

Answer 1

  m.id as id,
  max(m.name  ) as name,
  max(m.salary * 1000) as salary,
  max(m.plan * 1000) as plan,
  sum(coalesce(sale_positions.price * sale_positions.qty, 0)) as total
from managers m
  left join sales on (sales.manager_id = m.id) 
  left join sale_positions on (sale_positions.sale_id = sales.id)
group by m.id
order by 5 desc

БЛОГ НА HUSL

Запрос который по каждому менеджеру показывает сумму продаж

Ответы (1 шт):