SQL Server : how to return highest agregated columns

Question

Jow can I return two highest aggregated results?

For example, I have a result like this:

CustomerId   Total
---------------------
    5        1100.00
   n/a        100.00
    7         100.00
    6           0.00

and I need to return max 2 highest rows like this:

CustomerId    Total
-----------------------
    5         1100.00
    7          100.00
   n/a         100.00

I tried with TOP 2, but the problem is that I do not know how can I return more rows if there are rows with the same value.

Here is my query at the moment

SELECT
    ISNULL(CONVERT(varchar(50), u.CustomerId), 'not found') CustomerId ,
    ISNULL(SUM(o.Total), 0.00) Total
FROM 
    Orders o 
FULL OUTER JOIN 
    CustomerId u ON u.UserId = o.UserId
GROUP BY
    u.CustomerId 
ORDER BY 
    Total DESC;

GMB · Accepted Answer · 2020-06-12T16:55:48.960

Do you want WITH TIES?

SELECT TOP (2) WITH TIES
    ISNULL(CONVERT(varchar(50), u.CustomerId), 'not found') CustomerId ,
    ISNULL(SUM(o.Total), 0.00) Total
FROM Orders o 
FULL OUTER JOIN CustomerId u ON u.UserId = o.UserId
GROUP BY u.CustomerId 
ORDER BY total desc;

It is quite unclear why you actually need a FULL JOIN. Unless you have orphan orders, that should be a LEFT JOIN starting from the customers table and then going to the orders table. It is also unclear why you have a table named CustomerId, with a column that has the same name. Ideally you would name that table Customers instead, so:

SELECT TOP (2) WITH TIES
    c.CustomerId
    ISNULL(SUM(o.Total), 0.00) Total
FROM Customers c
LEFT JOIn Orders o ON u.UserId = c.UserId
GROUP BY c.CustomerId 
ORDER BY total desc;

That worked thank you. I didn’t think it would be that easy. — Dignity Dignity, Jun 12 '20 at 16:54

score 0 · Answer 2 · answered Jun 12 '20 at 16:52

First, an outer join should not be necessary, unless your data model is really messed.

You can use TOP 2 WITH TIES:

SELECT TOP (2) WITH TIES u.CustomerId, SUM(o.Total)
FROM Orders o JOIN
     CustomerId u 
     ON u.UserId = o.UserId
GROUP BY u.CustomerId 
Order by Total desc;

SQL Server : how to return highest agregated columns

2 Answers2