Запрос с несколькими COUNT

110
07 апреля 2021, 10:30

Я не специалист по БД. Мой уровень это простые запросы. Появилась задача выцепить некоторую статистику из старой CRM ранее использовавшейся в компании(сейчас переходим на новую и конечно экономим на профильных специалистах). Прошу вашей помощи в составлении запроса.

Есть три таблицы. БД MySQL

Таблица History - это заметки.

NOTE - Текст комментария (VARCHAR) 
DATECR - Дата создания комментария (DATE)
IDCREATOR - ID создателя (INTEGER)
TYPE - тип заметки  (INTEGER)

Таблица Users- пользователи

IDUSER - id юзера (INTEGER) 
NAME - ФИО юзера (VARCHAR)

Таблица Customers - компании

IDCUST - id компании 
IDUSER - id пользователя кто создал 
DATECR - дата создания

Задача: Получить статистику по списку юзеров (передается список IDUSER ) за определенную дату ( начальная и конечная даты):

Количество созданных компаний.
Количество созданных заметок с TYPE 1
Количество созданных заметок с TYPE 2
Количество созданных заметок с TYPE 3

Вывод: Хотелось бы получить таблицу с 5 столбцами(условные названия):

NAME(фио пользователя) | countCompany(кол-во созданных компаний) | countType1 | countType2 | countType3

Пользователь может быть с нулевой статистикой как по всем значениям так и только по некоторым, его в любом случае нужно отобразить в результатах.

С уважением, Waik

Answer 1
WITH 
company_stat AS ( SELECT iduser, COUNT(idcust) company_count
                  FROM customers
                  -- WHERE iduser IN (...) AND datecr BETWEEN ...
                  GROUP BY iduser ),
comment_stat AS ( SELECT idcreator, SUM(type=1) type1, SUM(type=2) type2, SUM(type=3) type3
                  FROM history
                  -- WHERE idcreator IN (...) AND  datecr BETWEEN ...
                  GROUP BY idcreator )
SELECT users.name, company_stat.company_count, comment_stat.type1, comment_stat.type2, comment_stat.type3
FROM users
LEFT JOIN company_stat ON users.iduser = company_stat.iduser
LEFT JOIN comment_stat ON users.iduser = comment_stat.idcreator
-- WHERE users.iduser IN (...)

на хостинге стоит mysql 5.6.43-84.3

SELECT users.name, company_stat.company_count, comment_stat.type1, comment_stat.type2, comment_stat.type3
FROM users
LEFT JOIN ( SELECT iduser, COUNT(idcust) company_count
            FROM customers
            -- WHERE iduser IN (...) AND datecr BETWEEN ...
            GROUP BY iduser ) AS company_stat ON users.iduser = company_stat.iduser
LEFT JOIN ( SELECT idcreator, SUM(type=1) type1, SUM(type=2) type2, SUM(type=3) type3
            FROM history
            -- WHERE idcreator IN (...) AND  datecr BETWEEN ...
            GROUP BY idcreator ) AS comment_stat ON users.iduser = comment_stat.idcreator
-- WHERE users.iduser IN (...)
READ ALSO
Как создать запрос MySQL “ALTER TABLE много мерный”?
MySQL

Как создать запрос MySQL “ALTER TABLE много мерный”?

Нужно в БД таблицы поменять в нескольких местах датуЗапрос сделал, все работает

 120
Как задать диапазон Int в Mysql?
MySQL

Как задать диапазон Int в Mysql?

Как сделать так, чтобы в строке с типом INT могло содержаться значение только в диапазоне от 0 до 100?

 124
Константы как имена полей базы данных?
PHP

Константы как имена полей базы данных?

Вопрос по константам

 124
php ошибки xmlreader, как обработать и убрать?
PHP

php ошибки xmlreader, как обработать и убрать?

Есть обработчик xml файлов, работает через php new xmlreader()Дак вот, есть проблема, пользователи частенько заливают левые кривые файлы, часто возникают...

 91