CODEWITHSUNDEEP
sqloracle-database
Paul Girtavic
Paul Girtavic

Reputation: 31

Oracle SQL query, getting a a maximum of a sum

Hey, guys. I'm struggling to solve one query, just cant get around it.

Basically, I got a some tables from data mart :

 DimTheatre(TheatreId(PK), TheatreNo, Name, Address, MainTel);
 DimTrow(TrowId(PK), TrowNo, RowName, RowType);
 DimProduction(ProductionId(PK), ProductionNo, Title, ProductionDir, PlayAuthor);
 DimTime(TimeId(PK), Year, Month, Day, Hour);
 TicketPurchaseFact( TheatreId(FK), TimeId(FK), TrowId(FK),
 PId(FK), TicketAmount);

The thing I'm trying to achieve in oracle is - I need to retrieve the most popular row type in each theatre by value of ticket sale

Thing I'm doing now is :

SELECT dthr.theatreid, dthr.name, max(tr.rowtype) keep(dense_rank last order 
by tpf.ticketamount), sum(tpf.ticketamount) TotalSale
FROM TicketPurchaseFact tpf, DimTheatre dthr, DimTrow tr
WHERE dthr.theatreid = tpf.theatreid
GROUP BY dthr.theatreid, dthr.name;

It does give me the output, but the 'TotalSale' column is totally out of place, it gives much way higher numbers than they should be.. How could I approach this issue :) ?

Upvotes: 1

Views: 73

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want the row type per theatre with the highest ticket amount. So join purchases and rows and then aggregate to get the total per rowtype. Use RANK to rank your row types per theatre and stay with the best ranked ones. At last join with the theatre table to get the theatre name.

select
  theatreid,
  t.name,
  tr.trowid
from
(
  select 
    p.theatreid, 
    r.rowtype, 
    rank() over (partition by p.theatreid order by sum(p.ticketamount) desc) as rn
  from ticketpurchasefact p
  join dimtrow r using (trowid)
  group by p.theatreid, r.rowtype
) tr
join dimtheatre t using (theatreid)
where tr.rn = 1;

Upvotes: 1

Vash
Vash

Reputation: 1787

I am not sure how MAX() KEEP () would help your case if I understand the problem correctly. But the below approach should work: 

SELECT x.theatreid, x.name, x.rowtype, x.total_sale
FROM
    (SELECT z.theatreid, z.name, z.rowtype, z.total_sale, DENSE_RANK() OVER (PARTITION BY z.theatreid, z.name ORDER BY z.total_sale DESC) as popular_row_rank
    FROM
        (SELECT dthr.theatreid, dthr.name, tr.rowtype, SUM(tpf.ticketamount) as total_sale
        FROM TicketPurchaseFact tpf, DimTheatre dthr, DimTrow tr
        WHERE dthr.theatreid = tpf.theatreid AND tr.trowid = tpf.trowid
        GROUP BY dthr.theatreid, dthr.name, tr.rowtype) z
    ) x
WHERE x.popular_row_rank = 1;

Upvotes: 1

Related Questions