mysql错误数据在子查询中分组和.它在所有日期都显示相同的金额

Question

SELECT collectionta.MonthYearAutoGen,
(SELECT sum(Collection) from accounts.collectionta where collectionta.modeId = 'HDFC 875 KM') as 'HDFC 875 KM',
(SELECT Sum(Collection) from accounts.collectionta where collectionta.modeId = 'synd 233 SET') as 'synd 233 SET',
(SELECT Sum(Collection) from accounts.collectionta where collectionta.modeId = 'Synd 217 KM') as 'Synd 217 KM'
FROM accounts.collectionta
GROUP BY collectionta.MonthYearAutoGen order by collectionta.DateInput;

在此处输入图像描述

enter code here

Answer 1

SELECT Date_format(DateInput, '%M %Y') As Month_Years, (SELECT sum(case when Date_format(DateInput, '%M %Y') = Month_Years  Then Collection else 0 end) from accounts.collectionta  where collectionta.modeId = 'HDFC 875 KM') as 'HDFC 875 KM', (SELECT sum(case when Date_format(DateInput, '%M %Y') = Month_Years  Then Collection else 0 end) from accounts.collectionta where collectionta.modeId = 'synd 233 SET') as 'synd 233 SET', (SELECT sum(case when Date_format(DateInput, '%M %Y') = Month_Years  Then Collection else 0 end) from accounts.collectionta where collectionta.modeId = 'Synd 217 KM') as 'Synd 217 KM', (SELECT sum(case when Date_format(DateInput, '%M %Y') = Month_Years  Then Collection else 0 end) from accounts.collectionta where collectionta.modeId = 'Cash') as 'Cash' FROM accounts.collectionta GROUP BY Month_Years order by collectionta.DateInput desc limit 12;