SQL Order by Total Within Group By

How to GROUP BY total amount for all items in order in one row

Just remove the total amount from the group by clause:

group by b.order_id --, d.total_amount

This guarantees just one row per order_id, while, on the other hand, having total_amount in the group by clause breaks into a new row for every different value of tuple (order_id, total_amount).

Side notes:

• use standard joins! old-school, implicit joins are legacy syntax, that should not be used in new code

• don't use single quotes for identifiers; use square brackets, or better yet, identifiers that do not require quoting

• don't store numbers as strings

So:

select lo.order_id
    string_agg(lt.testname, char(13))    as Test,
    string_agg(od.price, char(13))       as Price,
    string_agg(od.test_vat, char(13))    as [15% Vat],
    sum(convert(float, od.total_amount)) as Total
from patients p, 
inner join lab_orders lo    on p.patient_no = lo.patient_no 
inner join locustomers c    on lo.custid = c.custid
inner join order_details od on lo.order_id = od.order_id 
inner join labtests lt      on od.testid = lt.testid
where lo.order_id = 2000000272
group by lo.order_id

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!

sql sorting by subgroup sum data

If you are using SQL Server/Oracle/Postgresql you could use windowed SUM:

SELECT *
FROM tab
ORDER BY SUM(col3) OVER(PARTITION BY col) DESC, col2

LiveDemo

Output:

╔═════╦══════╦══════╗
║ col ║ col2 ║ col3 ║
╠═════╬══════╬══════╣
║ b   ║    1 ║   55 ║
║ b   ║    2 ║   44 ║
║ b   ║    3 ║    8 ║
║ a   ║    1 ║   15 ║
║ a   ║    2 ║    3 ║
║ a   ║    3 ║   34 ║
╚═════╩══════╩══════╝

SQL order groups by group subtotal

You can use a CTE coupled with SUM OVER() to achieve the desired sorting:

WITH Cte AS(
    SELECT
        ISNULL(ParentCo, 'ALL') AS ParentCo,
        ISNULL(Subsidiary, 'ALL') AS Subsidiary,
        SUM(Amount) AS SumOfAmount
    FROM tbl
    GROUP BY 
        ParentCo, Subsidiary
    WITH ROLLUP
),
CteFinal AS(
    SELECT *,
        sm = SUM(SumOfAmount) OVER(PARTITION BY ParentCo)
    FROM Cte
    WHERE
        ParentCo <> 'ALL'
)
SELECT
    ParentCo, Subsidiary, SumOfAmount
FROM CteFinal
ORDER BY
    sm DESC, SumOfAmount DESC

---

For your second question, you can simply use SUM OVER():

SELECT *,
    SumOfAmount = SUM(Amount) OVER(PARTITION BY ParentCo)
FROM tbl

SQL Server custom sort while grouping by max sum

Based in your description, you want to sort the ids by the maximum sum in any year. If that is the case, use window functions in the ORDER BY:

SELECT TOP 1000 YEAR(period) AS [Year], id_number, 
       SUM(ISNULL(amount, 0)) AS [Amount]
FROM table
WHERE YEAR(period) >= 2010 AND YEAR(period) < 2021
GROUP BY YEAR(period), id_number
ORDER BY MAX(SUM(ISNULL(amount, 0))) OVER (PARTITION BY id_number),
         id_number, YEAR;

The second sort key is needed so all rows for a given id_number() are together when there are ties.

Does the order of columns matter in a group by clause?

No, the order doesn't matter for the GROUP BY clause.

MySQL and SQLite are the only databases I'm aware of that allow you to select columns which are omitted from the group by (non-standard, not portable) but the order doesn't matter there either.

---

Related Topics