MySQL统计查询优化

Question

I'm trying to optimize my queries a bit but can't quite get it right.

I'm looking to get some statistics from a table

I have a query for the top contributors

SELECT `name`, `amount` FROM `customers` ORDER by `amount` DESC LIMIT 10

but I also want to get the SUM and AVG of amount in all records, not just the top 10. I'm not sure how to do this without having a new query for each.

Answer 1

If you want the top 10 users along with the SUM and AVG of ALL rows in the table, you can make a cartesian product of the SUM and AVG like so:

SELECT 
    a.name, a.amount, b.amtsum, b.amtavg
FROM customers a
CROSS JOIN (SELECT SUM(amount) AS amtsum, AVG(amount) AS amtavg FROM customers) b
ORDER BY amount DESC
LIMIT 10

Keep in mind that the values for SUM and AVG will be repeating on all 10 rows returned — they will be the same.

If you want the sum and averages of amount for each user, you can do:

SELECT a.name, a.amount, b.amtsum, b.amtavg
FROM customers a
INNER JOIN
(
    SELECT name, SUM(amount) AS amtsum, AVG(amount) AS amtavg
    FROM customers
    GROUP BY name
) b ON a.name = b.name
ORDER BY a.amount DESC
LIMIT 10