twoflower
@twoflower
php-разработчик

Почему mysql запрос возваращает неверные значение при выборке за временной период с разными годами?

Здравствуйте!
Есть mysql запрос (сгенерированный zf2)

SELECT `subt`.`from` AS `from`, SUM(ordered) AS `sum_ordered`, SUM(income) AS `sum_income`, SUM(sales_by_payment) AS 
`sum_sales_by_payment`
FROM (
SELECT `w`.`brand` AS `brand`, `w`.`article` AS `article`, 
`w`.`subject` AS `subject`, `w`.`size` AS `size`, `w`.`from` AS 
`from`, (
SELECT SUM(IFNULL(income,0)) AS `income`) AS `income`, (
SELECT SUM(IFNULL(income_sum_price,0)) AS `income_sum_price`) AS 
`income_sum_price`, (
SELECT SUM(IFNULL(ordered,0)) AS `ordered`) AS `ordered`, (
SELECT SUM(IFNULL(ordered_sum_price,0)) AS `ordered_sum_price`) AS 
`ordered_sum_price`, (
SELECT SUM(IFNULL(ordered_max_by_day,0)) AS `ordered_max_by_day`) AS 
`ordered_max_by_day`, (
SELECT SUM(IFNULL(return_before_payment,0)) AS 
`return_before_payment`) AS `return_before_payment`, (
SELECT SUM(IFNULL(return_before_payment_sum_price,0)) AS 
`return_before_payment_sum_price`) AS 
`return_before_payment_sum_price`, (
SELECT SUM(IFNULL(sales_by_payment,0)) AS `sales_by_payment`) AS 
`sales_by_payment`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0)) AS 
`sales_by_payment_sum_price`) AS `sales_by_payment_sum_price`, (
SELECT SUM(IFNULL(`return`,0)) AS ```return```) AS `return`, (
SELECT SUM(IFNULL(`return_sum_price`,0)) AS ```return_sum_price```) AS `return_sum_price`, (
SELECT SUM(IFNULL(`stock`,0)) AS ```stock```) AS `stock`, (
SELECT SUM(IF (sales_by_payment IS NOT NULL, 
sales_by_payment_sum_price / sales_by_payment, NULL)) AS 
`sale_price`) AS `sale_price`, (
SELECT SUM(income_sum_price * 0.62) AS `our_percent`) AS 
`our_percent`, (
SELECT SUM(IFNULL(income_sum_price,0) * 0.25) AS 
`cost_price_income`) AS `cost_price_select`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0) - 
(IFNULL(income_sum_price,0) * 0.25)) AS `profit`) AS `profit`, (
SELECT SUM(IF(income IS NOT NULL, (sales_by_payment / income) * 
 100, 0)) AS `sales_from_income`) AS `sales_from_income`, (
SELECT (SUM(sales_by_payment) / SUM(return_before_payment)) * 100 AS 
 `returns_buyout`) AS `returns_buyout`, (
SELECT SUM(return_before_payment) / SUM(ordered) * 100 AS `returns`) AS 
`returns`, (
SELECT SUM(sales_by_payment) / SUM(ordered) * 100 AS `buyout`) AS 
`buyout`, (
SELECT SUM(IFNULL(income,0) - IFNULL(sales_by_payment,0) + IFNULL(`return`,0)) AS `total`) AS `total`, (
SELECT (SUM(IFNULL(income,0)) - SUM(IFNULL(stock,0)) - SUM(IFNULL(`sales_by_payment`,0)) + SUM(IFNULL(`return`,0))) AS 
`in_the_way`) AS `in_the_way`
FROM `wildberries` AS `w`
WHERE `from` >= '2016-02-01' AND `end` <= '2017-05-04'
GROUP BY `article`
ORDER BY `article` DESC
LIMIT 20 OFFSET 0) AS `subt`
GROUP BY DAY(`from`)
ORDER BY `from` ASC


Если даты в условии одного года, запрос отрабатывает правильно. Но если даты берутся из разных лет (как в примере), возвращается всегда один ряд.

Схема таблицы:
CREATE TABLE `wildberries` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`brand` VARCHAR(50) NULL DEFAULT NULL,
	`subject` VARCHAR(50) NULL DEFAULT NULL,
	`article` VARCHAR(50) NULL DEFAULT NULL,
	`size` VARCHAR(50) NULL DEFAULT NULL,
	`income` VARCHAR(50) NULL DEFAULT NULL,
	`income_sum_price` VARCHAR(50) NULL DEFAULT NULL,
	`ordered` VARCHAR(50) NULL DEFAULT NULL,
	`ordered_sum_price` VARCHAR(50) NULL DEFAULT NULL,
	`ordered_max_by_day` VARCHAR(50) NULL DEFAULT NULL,
	`return_before_payment` VARCHAR(50) NULL DEFAULT NULL,
	`return_before_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
	`sales_by_payment` VARCHAR(50) NULL DEFAULT NULL,
	`sales_by_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
	`return` VARCHAR(50) NULL DEFAULT NULL,
	`return_sum_price` VARCHAR(50) NULL DEFAULT NULL,
	`stock` VARCHAR(50) NULL DEFAULT NULL,
	`source_file` VARCHAR(50) NULL DEFAULT NULL,
	`from` DATE NULL DEFAULT NULL,
	`end` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `article` (`article`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


Посоветуйте, пожалуйста, в чем может быть дело?
  • Вопрос задан
  • 146 просмотров
Пригласить эксперта
Ответы на вопрос 1
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
GROUP BY DAY(`from`)
И без разницы, какой год или месяц, данные всегда свернутся по дням месяца, то есть все первые числа всех месяцев всех лет попадут в одну строку.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы