Как конвертировать DateTime в Date для группировки по дате в запросе SQL — DELPHI?

Question

[Владимир Кай]

Добрый день уважаемые знатоки!

Наткнулся на проблему с группировкой результатов SQL-запроса по дате.
Структура таблицы:
id - номер заказа;
price - общая стоимость;
date_insert - дата заказа; - тип DateTime

Формирую запрос на расчет среднего чека и количество заказов:

SELECT id, date_insert, SUM(price), AVG(price), COUNT(id)
FROM b_sale_order
WHERE (canceled = 'N') AND (date_insert BETWEEN :StartD AND :EndD)
ORDER BY date_insert
GROUP BY date_insert

Собственно группировка не выполняется, так как запрос выпадет с ошибкой в группировке по полю Даты.
Вычитал что проблема в том, что у меня тип данных для этого поля DateTime, а не простой Date.
И требуется конвертировать или отбросить часть записи со временем.

Как это сделать чтобы получился корректный SQL_запрос с группировкой по полю Даты?
Данные нужно будет загонять в TeeChart, поэтому без группировки никак..

Answer 1

[Владимир Кай]

Собственно работает всё.

Вот итоговый запрос:

SELECT date_format(date_insert, "%Y.%m.%d") as Date, Sum(price), AVG(price) as SrCheck
FROM b_sale_order 
WHERE (canceled = 'N') AND (date_insert BETWEEN '2017.01.01' AND '2017.01.31') 
GROUP BY Date 
ORDER BY Date

Answer 2

[Rsa97]

Ошибка в том, что ORDER BY и GROUP BY должны идти в обратном порядке.
Для группировки по дате надо так:

SELECT DATE(`date_insert`) AS `date`, SUM(`price`), AVG(`price`), COUNT(*)
  FROM `b_sale_order`
  WHERE (`canceled` = 'N') AND (`date_insert` BETWEEN :StartD AND :EndD)
  GROUP BY `date`
  ORDER BY `date`

`id` выбирать бесполезно, он будет взят от первой попавшейся строки в группе. Индекс в GROUP BY и ORDER BY работать не будет, поскольку используется не значение поля, а функция от него.

Comments

[Владимир Кай]

И какой есть выход?

[Rsa97]

Владимир Кай: Для индексов - создать отдельное поле, (в MySQL 5.7.6 и выше можно генерируемое), по которому уже и строить индекс.

[Владимир Кай]

Rsa97: А если вот так:

SELECT id, canceled, price, date_format(date_insert, '%Y.%m.%d') as Date 
FROM b_sale_order 
WHERE (canceled = 'N') AND (date_insert BETWEEN '2017.01.01' AND '2017.01.31') 
GROUP BY date_insert 
ORDER BY date_insert

[Владимир Кай]

Rsa97: Запрос корректно отрабатывает на хостинге, а в Delphi ругается что поля date_insert нет в Query

[Владимир Кай]

Владимир Кай: Собственно так и работает всё.
Проблема была в DBChart'e который использовал неверное поле.

Вот итоговый запрос:

SELECT date_format(date_insert, "%Y.%m.%d") as Date, Sum(price), AVG(price) as SrCheck
FROM b_sale_order 
WHERE (canceled = 'N') AND (date_insert BETWEEN '2017.01.01' AND '2017.01.31') 
GROUP BY Date 
ORDER BY Date