Я пытаюсь суммировать приведенную ниже таблицу в одну строку с 3Month, 6Month, 9Month и так далее, но застрял с ошибкой mysql. Я просмотрел другие похожие сообщения и заменил псевдоним GROUP BY полным регистром, но все еще говорит, что содержит неагрегированный столбец. Любая помощь будет оценена.
mysql> SELECT ith.ItemCode, ith.TransactionDate, DATEDIFF(CURDATE(), ith.TransactionDate) AS Date_Diff,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 0_90_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 91_180_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 181_270_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over_270_Days
->
-> FROM im_itemtransactionhistory ith, ci_item i
-> WHERE ith.TransactionCode = 'PO'
-> AND ith.TransactionDate >= '2018-01-01'
-> AND ith.ItemCode = i.ItemCode
-> AND i.TotalQuantityOnHand > 0
-> AND ith.ItemCode = 512619
-> GROUP BY ith.ItemCode, ith.TransactionDate, ith.TransactionQty
->
-> ORDER BY ith.TransactionDate ASC;
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
| ItemCode | TransactionDate | Date_Diff | 0_90_Days | 91_180_Days | 181_270_Days | Over_270_Days |
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
| 512619 | 2018-03-19 | 220 | 0 | 0 | 1200 | 0 |
| 512619 | 2018-05-10 | 168 | 0 | 3600 | 0 | 0 |
| 512619 | 2018-05-11 | 167 | 0 | 600 | 0 | 0 |
| 512619 | 2018-06-15 | 132 | 0 | 3600 | 0 | 0 |
| 512619 | 2018-06-26 | 121 | 0 | 2400 | 0 | 0 |
| 512619 | 2018-07-23 | 94 | 0 | 2400 | 0 | 0 |
| 512619 | 2018-07-26 | 91 | 0 | 1200 | 0 | 0 |
| 512619 | 2018-09-10 | 45 | 1200 | 0 | 0 | 0 |
| 512619 | 2018-09-10 | 45 | 2400 | 0 | 0 | 0 |
| 512619 | 2018-10-16 | 9 | 1092 | 0 | 0 | 0 |
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
И это то, что я пробовал ..
SELECT ith.ItemCode, ith.TransactionCode, i.TotalQuantityOnHand, DATEDIFF(CURDATE(), ith.TransactionDate),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 0_90_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 91_180_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 181_270_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over_270_Days,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 3m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 6m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 9m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over9m_total
FROM im_itemtransactionhistory ith, ci_item i
WHERE ith.TransactionCode = 'PO'
AND ith.TransactionDate >= '2018-01-01'
AND ith.ItemCode = i.ItemCode
AND i.TotalQuantityOnHand > 0
AND ith.ItemCode = 512619
GROUP BY ith.ItemCode,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END)
ORDER BY ith.TransactionDate ASC
Код ошибки вернет
#1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mas90.ith.TransactionDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
И это результат, который я ищу ..
+----------+-----------+-------------+--------------+---------------+
| ItemCode | 0_90_Days | 91_180_Days | 181_270_Days | Over_270_Days |
+----------+-----------+-------------+--------------+---------------+
| 512619 | 4692 | 13800 | 1200 | 0 |
+----------+-----------+-------------+--------------+---------------+
не могли бы вы показать желаемый результат (и явно описать, в каком порядке вы ожидаете результат)
Код ошибки добавлен спасибо!
Также добавлен желаемый результат!
Некоторое время назад у меня возникла похожая проблема. Проверить stackoverflow.com/questions/52080167/…
BTW: пожалуйста, используйте явное JOIN.
См. meta.stackoverflow.com/questions/333952/…
Огромное спасибо, Роберт Кок! После некоторых проб и ошибок мне удалось обернуть CASE в FROM и применить псевдоним к GROUP BY. Вы только что спасли мне день, еще раз спасибо!
С помощью Роберта Кока я смог решить эту проблему с помощью запроса ниже.
SELECT ith.ItemCode, TEST1.total, TEST2.0_90_Days, TEST3.91_180_Days, TEST4.181_270_Days, TEST5.Over_270_Days
FROM im_itemtransactionhistory ith,
(SELECT ith.ItemCode, SUM(ith.TransactionQty) AS total
FROM im_itemtransactionhistory ith
WHERE ith.TransactionCode = 'PO'
AND ith.ItemCode = 512619
AND ith.TransactionDate >= '2018-01-01'
GROUP BY ith.ItemCode
) AS TEST1,
(SELECT ith.ItemCode, SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 0_90_Days
FROM im_itemtransactionhistory ith
WHERE ith.TransactionCode = 'PO'
AND ith.ItemCode = 512619
AND ith.TransactionDate >= '2018-01-01'
GROUP BY ith.ItemCode
) AS TEST2,
(SELECT ith.ItemCode, SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 91_180_Days
FROM im_itemtransactionhistory ith
WHERE ith.TransactionCode = 'PO'
AND ith.ItemCode = 512619
AND ith.TransactionDate >= '2018-01-01'
GROUP BY ith.ItemCode
) AS TEST3,
(SELECT ith.ItemCode, SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 181_270_Days
FROM im_itemtransactionhistory ith
WHERE ith.TransactionCode = 'PO'
AND ith.ItemCode = 512619
AND ith.TransactionDate >= '2018-01-01'
GROUP BY ith.ItemCode
) AS TEST4,
(SELECT ith.ItemCode, SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over_270_Days
FROM im_itemtransactionhistory ith
WHERE ith.TransactionCode = 'PO'
AND ith.ItemCode = 512619
AND ith.TransactionDate >= '2018-01-01'
GROUP BY ith.ItemCode
) AS TEST5
WHERE ith.TransactionCode = 'PO'
AND ith.TransactionDate >= '2018-01-01'
AND ith.ItemCode = 512619
AND ith.ItemCode = TEST1.ItemCode
AND ith.ItemCode = TEST2.ItemCode
GROUP BY ith.ItemCode, TEST2.0_90_Days, TEST3.91_180_Days, TEST4.181_270_Days, TEST5.Over_270_Days
И публикация результатов надеюсь, что это поможет кому-то с такими же проблемами.
+----------+-------+-----------+-------------+--------------+---------------+
| ItemCode | total | 0_90_Days | 91_180_Days | 181_270_Days | Over_270_Days |
+----------+-------+-----------+-------------+--------------+---------------+
| 512619 | 19692 | 4692 | 13800 | 1200 | 0 |
+----------+-------+-----------+-------------+--------------+---------------+
Пожалуйста, добавьте описание ошибки в свой вопрос