MySQL - How do I get SUM of a COUNT of a field?
I'm using the following statement and getting almost everything I need. In addition, I'd like to get the SUM of all of the row_total results.
SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger,
COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain,
SUM(c.amount) as row_total
FROM campaigns c
JOIN campaign_codes cc
LEFT JOIN partners p on p.id = 4
WHERE c.partner_id = 4 AND cc.status = 0 AND c.id = cc.campaign_id
GROUP BY c.id
ORDER BY campaign_amount ASC
And here's an example result, with the last column being the column I want to add together:
1 10.00 30 992 2开发者_开发技巧4 9920.00
3 25.00 30 995 290 24875.00
16 50.00 30 988 335 49400.00
17 100.00 30 1000 335 100000.00
To get the running sum:
SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger,
COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain,
SUM(c.amount) as row_total, @r := @r + SUM(c.amount) AS running_sum
FROM (
SELECT @r := 0
) vars,
campaign c, …
To get the grand total (and all other aggregates) as an additional record:
SELECT *
FROM (
SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger,
COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain,
SUM(c.amount) as row_total
FROM campaign c, …
GROUP BY
c.id WITH ROLLUP
) q
ORDER BY
campaign_amount ASC
To get the total sum in an additional field:
SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger,
COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain,
SUM(c.amount) as row_total,
(
SELECT SUM(c.amount)
FROM campaign c, …
-- the same but without the GROUP BY and ORDER BY clauses.
)
FROM campaign c, …
GROUP BY
c.id
ORDER BY
campaign_amount ASC
Encapsulate your query like:
SELECT SUM([row_total]) FROM (
Your query
) Source
From the comments below, you could try...
Your Query
UNION ALL
SELECT '', '', '', '', '', SUM([row_total]) FROM (
Your query
) Source
The union all will allow you to append an additional row to the result set which will give you a 'total row'. You can change the empty fields (the single quotes) to match your data types or assign them special values that will represent your total row.
SELECT TB.campaign_id, TB.campaign_amount, TB.warning_trigger, TB.code_count, TB.days_remain, TB.row_total, SUM(TB.row_total) GlobalTotal FROM (SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger,
COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain,
SUM(c.amount) as row_total
FROM campaigns c
JOIN campaign_codes cc
LEFT JOIN partners p on p.id = 4
WHERE c.partner_id = 4 AND cc.status = 0 AND c.id = cc.campaign_id
GROUP BY c.id
ORDER BY campaign_amount ASC) TB
GROUP BY TB.campaign_id, TB.campaign_amount, TB.warning_trigger, TB.code_count, TB.days_remain, TB.row_total
精彩评论