Sum a generated list in MySQL, in a single query
I need to sum a result that I'm getting from an existing query. And the it has to extend the current query and remain a single query
(by this I mean NOT - DO 1; DO 2; DO3;)
My current query is:
SELECT SUM((count)/(SELECT COUNT(*) FROM mobile_site_statistics WHERE campaign_id='1201' AND start_time BETWEEN CURDATE()-1 AND CURDATE())*100) AS percentage FROM mobile_site_statistics WHERE device NOT LIKE '%Pingdom%' AND campaign_id='1201' AND start_time BETWEEN (CURDATE()-1) AND CURDATE() GROUP BY device ORDER BY 1 DESC LIMIT 10;
This returns:
+------------+
| percentage |
+------------+
| 47.3813 |
| 19.7940 |
| 5.6672 |
| 5.0801 |
| 3.960开发者_开发问答3 |
| 3.8500 |
| 3.1294 |
| 2.9924 |
| 2.9398 |
| 2.7136 |
+------------+
What I need is the total of that table (total percent used by the top 10 devices)(that's all) but it has to be a single query (Has to include the initial query)(Has to be a single query due to another program that's using the query)
Is this possible? every way I have tried so far has failed. We tried temporary tables, but that turned into multiple queries.
Just do a
SELECT SUM(percentage) AS total FROM (<YOUR_QUERY>) a
and replace the sub-query <YOUR_QUERY>
with your initial query
精彩评论