mysql 子查询分组,在子查询中使用分组方式

I am trying to display progressive_total and cumulative_sum group by the subincome field in house_details table using mysql. I built my schema in this

working query:

SELECT *,

COALESCE(

(SELECT SUM(x.rupees)

FROM house_details x

WHERE MONTH(x.date) < t1.month), '-') AS progressive_total,

(SELECT SUM(x.rupees)

FROM house_details x

WHERE MONTH(x.date) <= t1.month) AS cumulative_sum

FROM

(SELECT MONTHNAME(t.date) AS `monthname`,

MONTH(t.date) `month`,

YEAR(t.date) AS YEAR,

t.income,

t.subincome,

t.ssubincome,

SUM(rupees) AS amount,

GROUP_CONCAT(receipt_id) AS receipt_ids

FROM house_details t

WHERE YEAR(t.date) = YEAR(CURRENT_DATE())

GROUP BY month(t.date),

t.subincome

ORDER BY t.date) t1

but this gives irrelevant cumulative_sum in the field.

I tried to use group by inside the subquery like this:

query:

SELECT *,

COALESCE(

(SELECT SUM(x.rupees)

FROM house_details x

WHERE MONTH(x.date) < t1.month

GROUP BY x.subincome), '-') AS progressive_total,

(SELECT SUM(x.rupees)

FROM house_details x

WHERE MONTH(x.date) <= t1.month

GROUP BY x.subincome) AS cumulative_sum

FROM

(SELECT MONTHNAME(t.date) AS `monthname`,

MONTH(t.date) `month`,

YEAR(t.date) AS YEAR,

t.income,

t.subincome,

t.ssubincome,

SUM(rupees) AS amount,

GROUP_CONCAT(receipt_id) AS receipt_ids

FROM house_details t

WHERE YEAR(t.date) = YEAR(CURRENT_DATE())

GROUP BY month(t.date),

t.subincome

ORDER BY t.date) t1;

but it shows error sub query returns more than one row.

解决方案

You can use below query for your expected result set

SELECT *,

COALESCE(

(SELECT SUM(pt.rupees) FROM (

SELECT MONTH(`date`) `month`,

MAX(id) id,

SUM(rupees) rupees

FROM house_details

GROUP BY `month`,subincome

) pt

WHERE CASE WHEN pt.month = t1.month THEN pt.id < t1.id ELSE pt.month < t1.month END

), 0) AS progressive_total,

(SELECT SUM(rupees) FROM(

SELECT MONTH(`date`) `month`,

MAX(id) id,

SUM(rupees) rupees

FROM house_details

GROUP BY `month`,subincome

) cs

WHERE CASE WHEN cs.month = t1.month THEN cs.id <= t1.id ELSE cs.month <= t1.month END

) AS cumulative_sum

FROM (

SELECT MONTHNAME(t.date) AS `monthname`,

MAX(id) id,

MONTH(t.date) `month`,

YEAR(t.date) AS `year`,

GROUP_CONCAT(t.income) income,

t.subincome,

GROUP_CONCAT(t.ssubincome) ssubincome,

SUM(rupees) AS amount,

GROUP_CONCAT(receipt_id) AS receipt_ids

FROM house_details t

WHERE YEAR(t.date) = YEAR(CURRENT_DATE())

GROUP BY `monthname`,`month`, t.subincome

ORDER BY `month`

) t1