CODEWITHSUNDEEP
sqloracle-database
vga1232
vga1232

Reputation: 13

How to get the maximum of the sum of the sales in SQL?

I have a simple assignment but I am stuck, I have a table and need to print out the ID with the maximum total of sales. I have managed to print a sorted list of the IDs based on the sum of each one's sales:

SELECT "COMPANY"."ID", SUM("COMPANY"."PRICE") As PriceSum
FROM "COMPANY"
WHERE "COMPANY"."DATEOFSALE" >= DATE '2016-01-01'
GROUP BY "COMPANY"."ID"
ORDER BY PriceSum DESC;

I just want to show the ID and the total sales of the top selling company. TIA

This is in Oracle, so I can't be cheap and use LIMIT 1.

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use a subquery instead:

SELECT c.*
FROM (SELECT "COMPANY"."ID", SUM("COMPANY"."PRICE") As PriceSum
      FROM "COMPANY"
      WHERE "COMPANY"."DATEOFSALE" >= DATE '2016-01-01'
      GROUP BY "COMPANY"."ID"
      ORDER BY PriceSum DESC
     ) c
WHERE rownum = 1;

In Oracle 12c+, you can use FETCH FIRST 1 ROW ONLY without the subquery. This is the ANSI standard equivalent of LIMIT.

EDIT:

If you want all companies with the maximum, use rank() or dense_rank():

SELECT c.*
FROM (SELECT "COMPANY"."ID", SUM("COMPANY"."PRICE") As PriceSum,
             RANK() OVER (ORDER BY SUM("COMPANY"."PRICE") DESC) as seqnum
      FROM "COMPANY"
      WHERE "COMPANY"."DATEOFSALE" >= DATE '2016-01-01'
      GROUP BY "COMPANY"."ID"
      ORDER BY PriceSum DESC
     ) c
WHERE seqnum = 1;

You can replace RANK() with ROW_NUMBER() and get the previous result as well.

Upvotes: 2

Related Questions