Значение строки MySQL SUM по идентификатору и дате

Что мне нужно выполнить, я суммирую значение столбца таблицы MySQL на основе значения столбца VARCHAR и диапазона лет. Возможно, это сбивает с толку, поэтому я приведу простой пример:

Столбцы: id (бесполезно для этого), fruit_type, date_of_sale, sale_type, price

Базовый запрос:

SELECT * FROM fruits

дает мне :

  1  APPLES   2018-05-30  GOOD   50.50 
  2  APPLES   2018-05-30  BAD   -20.50 
  3  APPLES   2018-05-30  GOOD   40.00 
  4  APPLES   2018-05-30  BAD   -10.50 
  5  APPLES   2017-05-29  GOOD   39.50 
  6  APPLES   2017-05-29  BAD   -10.00 
  7  APPLES   2017-05-29  GOOD   30.00 
  8  APPLES   2017-05-29  BAD   -20.00 
  9  BANANAS  2018-05-29  GOOD   20.00 
 10  BANANAS  2018-05-29  BAD    20.00 
 11  BANANAS  2018-05-29  GOOD   10.00 
 12  BANANAS  2018-05-29  BAD    -5.00 
 13  BANANAS  2017-05-29  GOOD   50.00 
 14  BANANAS  2017-05-29  BAD    -3.00 
 15  BANANAS  2017-05-29  GOOD   10.00 
 16  BANANAS  2017-05-29  BAD    -3.00 
 17  ORANGES  2018-05-28  GOOD    5.00 
 18  ORANGES  2018-05-28  BAD    -1.00 
 19  ORANGES  2017-05-28  GOOD   10.00 
 20  ORANGES  2017-05-28  BAD    -1.00

Что мне нужно сделать:

  Fruit      Sales 2017    Bad Sales 2017    Total 2017    Sales 2018    Bad Sales 2018    Total 2018       
  APPLES     69.50         -30.00            39.50         90.50         -30.50            60.00            
  BANANAS    60.00         -6.00             54.00         30.00         -10.00            20.00            
  ORANGES    10.00         -1.00             9.00          5.00          -1.00             5.00

Запрос, который я пробовал:

SELECT fruit_type AS Fruit,\
(SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Sales_2017,\
(SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Bad_Sales_2017,\
(SELECT SUM(Sales_2017-Bad_Sales_2017)),\
(SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Sales_2018,\
(SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Bad_Sales_2018,\
(SELECT SUM(Sales_2018-Bad_Sales_2018)),\
FROM fruits
GROUP BY fruit_type;

Проблема в том, что значения, возвращаемые запросом, представляют собой общую СУММУ всего столбца со значением ХОРОШО или ПЛОХО, вместо значения ХОРОШО по соответствию типа фруктов.

Нужны советы по этой проблеме, плз.

Пропустите подзапросы. Вместо этого используйте выражения case для условного агрегирования.

— 30.05.2018 10:46

Я думаю, вы можете использовать кодиционную сумму, например сумму выбора (случай, когда sale_type = 'GOOD' И date_of_sale МЕЖДУ '2017-01-01' И '2017-12-31', тогда цена еще 0) ... и так далее

— 30.05.2018 10:47

Добавьте в подзапрос условие fruit_type. Допустим, псевдоним фруктов - f1, а в подзапросе псевдоним фруктов - f2, тогда добавьте f1.fruit_type = f2.fruit_type.

— 30.05.2018 10:48

Серьезно подумайте о решении проблем отображения данных в коде приложения

— 30.05.2018 11:03

mysql sql

30.05.2018 10:40

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

109

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Попробуйте это, просто добавьте больше подзапросов для каждой нужной строки.

SELECT all2017.fruit_type as Fruit
,Sales_2017
,Bad_Sales_2017
from (SELECT fruit_type
,SUM(ABS(price)) Sales_2017
FROM fruits
WHERE year(date) = 2017
GROUP BY fruit_type) as all2017
join (SELECT fruit_type
,SUM(ABS(price)) Bad_Sales_2017
FROM fruits
WHERE year(date) = 2017
AND sales_type = 'BAD'
GROUP BY fruit_type) as bad2017
on all2017.fruit_type = bad2017.fruit_type

30.05.2018 10:58

Ответ принят как подходящий

используйте sum + case when condition для подсчета ваших продаж

попытайся:

select 
  fruit_type AS Fruit,
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      case when sale_type = 'GOOD' then price else 0 end
    else 0 end
  ) "Sales 2017",
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      case when sale_type = 'BAD' then price else 0 end
    else 0 end
  ) "Bad Sales 2017", 
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      price
    else 0 end
  ) "Total 2017",  
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      case when sale_type = 'GOOD' then price else 0 end
    else 0 end
  ) "Sales 2018",
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      case when sale_type = 'BAD' then price else 0 end
    else 0 end
  ) "Bad Sales 2018", 
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      price
    else 0 end
  ) "Total 2018"
FROM fruits
GROUP BY fruit_type;

Ссылка на демонстрацию SQL Fiddle

Спасибо сработало как по волшебству

— 30.05.2018 11:46

30.05.2018 11:04

SELECT T.FRUIT, 
  SUM(IF(T.YEAR='2017' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2017`,
  SUM(IF(T.YEAR='2017' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2017`,
  SUM(IF(T.YEAR='2017',T.price_per_year,0)) `Total 2017`,
  SUM(IF(T.YEAR='2018' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2018`,
  SUM(IF(T.YEAR='2018' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2018`,
  SUM(IF(T.YEAR='2018',T.price_per_year,0)) `Total 2018`
FROM (SELECT
  FRUIT_TYPE FRUIT,
  YEAR(date_of_sale) YEAR,
  sale_type,
  SUM(price) price_per_year
FROM fruits 
GROUP BY 
FRUIT_TYPE,
YEAR(date_of_sale),
sale_type) T
GROUP BY FRUIT
ORDER BY T.FRUIT;

Смотрите рабочий ДЕМО на SQL Fiddle.

Поправил @ Strawberry :) Спасибо за ссылку

— 30.05.2018 11:43

30.05.2018 11:09