How to Order by a Sum() in MySQL

How to ORDER BY a SUM() in MySQL?

Don'y forget that if you are mixing grouped (ie. SUM) fields and non-grouped fields, you need to GROUP BY one of the non-grouped fields.

Try this:

SELECT SUM(something) AS fieldname
FROM tablename
ORDER BY fieldname

OR this:

SELECT Field1, SUM(something) AS Field2
FROM tablename
GROUP BY Field1
ORDER BY Field2

And you can always do a derived query like this:

SELECT
   f1, f2
FROM
    (
        SELECT SUM(x+y) as f1, foo as F2
        FROM tablename 
        GROUP BY f2
    ) as table1
ORDER BY 
    f1

Many possibilities!

Order by sum(value) sql

You did not group the columns. You need to group it by non-aggregated column and in this case by blog_id

SELECT blog_id, sum(amount) TotalSum
FROM   donations 
GROUP  BY blog_id
ORDER  BY TotalSum

The reason why your query executed well without throwing an exception is because mysql permits to use aggregate function without specifying non-aggregated column in the GROUP BY clause.

• see MySQL Extensions to GROUP BY

MYSQL Order By Sum of Columns

Why not try before concluding it doesn't work? In point of fact, it does.

Find SUM of ALL items within a Order mysql

SELECT o.id AS oid, o.date AS date, SUM(i.price*i.qty) AS total_per_order_date 
  FROM orders o 
INNER JOIN items i 
    ON o.id = i.orderid 
 GROUP BY o.id,o.date 
ORDER BY o.id,o.date ;

Sum price from products to order

You need to make SUM on products.price * order_products.quantity and GROUP BY on order_products.order_id

SELECT
    SUM(products.price * order_products.quantity) AS total_order_price,
    orders.*,
    customers.*
FROM
    orders
    LEFT JOIN order_products ON orders.order_id = order_products.order_id
    LEFT JOIN products ON order_products.product_id = products.product_id
    LEFT JOIN customers USING orders.customer_id = customers.customer_id
WHERE
    order_id = 20001 
GROUP BY
    order_products.order_id 
ORDER BY
    order_id DESC

Read more about GROUP BY modifier:
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

Order By Sum of Two Fields

Very simple

SELECT 
ID, KARMA_UP, KARMA_DOWN, (KARMA_UP-KARMA_DOWN) AS USER_KARMA 
FROM KARMA 
ORDER BY USER_KARMA DESC

mysql - total sum of all grouped count(*) 's with order by and limit (fiddle included)

You can aggregate and limit first in a subquery, then compute the grand total:

SELECT t.*, SUM(total) OVER() AS TotalSum
FROM (
    SELECT event_target AS Name, COUNT(*) AS Total
    FROM data_logs
    GROUP BY Name
    ORDER BY Total DESC
    LIMIT 10
) t

MySQL Order by sum of another table

Thank you juergen d

Your query was almost perfect:

here is a slightly modified version which will do the ordering correctly:

SELECT comments.id, 
       COUNT(comment_rating.id) AS rating_count,
       COALESCE(SUM(positive),0) as rating
FROM comments 
LEFT JOIN comment_rating ON comments.id = comment_rating.comment_id 
GROUP BY comments.id 
ORDER BY rating DESC

This way if the row doesnt exist it will be set to zero rather than null and will be ordered correctly.

---

Related Topics