Hi Folks,
I have a simple query request that I need to perform in my data warehouse
(well I am sure it is simple for you guys, but I am no to this )
The requirement is: Show the TOP 2 theatres by the value of ticket sales.
This information is contained in my fact table: FactTicketPurchase. Here is a sample of the data:
Select * from TicketPurchase:
| TICKETPURCHASEID | CLIENTID | PRODUCTIONID | THEATREID | TIMEID | TROWID | SUMTOTALAMOUNT |
|---|
| 60006 | 2527 | 66 | 21 | 942 | 40 | 7 |
| 60007 | 2527 | 72 | 21 | 988 | 36 | 6 |
| 60008 | 2527 | 74 | 21 | 1001 | 40 | 6 |
| 60009 | 2527 | 76 | 21 | 1015 | 37 | 6 |
| 60010 | 2527 | 79 | 21 | 1037 | 39 | 6 |
| 60011 | 2527 | 79 | 21 | 1038 | 37 | 7 |
So here is the initial query I envisaged (which I believe is correct?)
SELECT TheatreID, SUM(SumtotalAmount) SumTotalAmount
FROM FactTicketPurchase
GROUP BY TheatreID
ORDER BY SumTotalAmount DESC;
The above query returned to me 6 rows (that is, one for each Theatre registered in the fact table):
| THEATREID | SUMTOTALAMOUNT |
|---|
| 21 | 20908 |
| 25 | 19365 |
| 22 | 15232 |
| 23 | 14022 |
| 24 | 13667 |
| 26 | 13028 |
However, since the requirement is to return only the TOP 2 theatres, I would like my query above to only return the top two rows, which are the theatres whose IDs are 21 and 25.
I tried using ROWNUM but that doesn't give the result I expected (and I know why! makes sense ) But I don't know then how to obtain the result I'd like.
Could someone please advise?
Regards,
P.