Cannot query top 25 summed values

Question

I'm having difficulty querying the top 25 values. My table contains transaction dates, locations, UPC number, and sales quantity.

This is what I have so far -

SELECT date_id as "Date", store_id as "Store", sum(Sales_Unit) as "Total Sales" FROM fact_pos_sales GROUP BY date_id, store_id ORDER BY "Total Sales" DESC ;

This is what I'm hoping to get as my result, except for just the top 25 values. I've tried adding "Top 25" next to Select, or adding a "Limit 25" at the end of my sql, except none of these two methods work. I'm using Oracle SQL Developer, for what it's worth.

Current output, without the limit in place

Would someone be able to advise? I've been trying to search for the answers on Stockoverflow and elsewhere for the past two hours, with no results. Thank you!

`TOP` is for SQL Server, `LIMIT` for MySQL, in Oracle you can use `rownum` — Alberto Martinez, Sep 15 '17 at 22:19
existing answer from before, I think it's what you need https://stackoverflow.com/a/2498058/2175524 — Goran B., Sep 15 '17 at 22:28

Ousmane D. · Answer 1 · 2017-09-15T23:08:32.110

Not all database systems support the SELECT TOP clause (that's for SQL Server). MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

Therefore something like:

SELECT * From 
 (
     SELECT date_id as "Date", store_id as "Store", sum(Sales_Unit) as "Total Sales" 
     FROM fact_pos_sales 
     GROUP BY date_id, store_id 
     ORDER BY "Total Sales" DESC
 ) 
WHERE ROWNUM <= 25;

score 0 · Answer 2 · answered Sep 15 '17 at 22:20

0

Select * From
(
   SELECT date_id as "Date", 
          store_id as "Store", 
          sum(Sales_Unit) as "Total Sales" 
     FROM fact_pos_sales 
    GROUP BY date_id, store_id 
    ORDER BY "Total Sales" DESC ;
) 
Where RowNum <=25

answered Sep 15 '17 at 22:20

Hüseyin Burak Karadag

1,848
17
23

score 0 · Answer 3 · answered Sep 15 '17 at 22:38

0

In Oracle 12c:

SELECT date_id as "Date", 
      store_id as "Store", 
      sum(Sales_Unit) as "Total Sales" 
FROM fact_pos_sales 
GROUP BY date_id, store_id 
ORDER BY "Total Sales" DESC
FETCH FIRST 25 ROWS ONLY;

answered Sep 15 '17 at 22:38

Bobby Durrett

1,223
12
19

Cannot query top 25 summed values

3 Answers3