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.
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
try
SELECT `name`, `amount`, sa.sum_a, sa.avg_a
FROM `customers`, (select sum(amount) as sum_a, avg(amount) as avg_a from customers) sa
ORDER by `amount` DESC
LIMIT 10
One option is to use subqueries
SELECT
`name`, `amount`,
(SELECT SUM(amount) FROM customers) as summation,
(SELECT AVG(amount) FROM customers) as average
FROM
`customers` ORDER by `amount` DESC LIMIT 10
The cleanest and typically also fastest way in this case are the two queries. There is no advantage in fetching the same aggregates ten times.
Make sure that you have an index on amount. That will save you full sorting of the table with each query.
You can use the MySQL Explain keyword for query optimization.
For more details you can click here