CODEWITHSUNDEEP
sqloracle-databasecountmax
Greek Freak
Greek Freak

Reputation: 385

using MAX in a SQL query

I am trying to get the MAX count for a specific employee at a specific store. I can easily get the counts of each employee and what store they work at but I just want the top 1 at each store.

SELECT Emp_ID || ': ' || First_Name || ' ' || Last_Name AS "Emp. Name",
        Store_Num || ': ' || Store_Name AS "Store",
        COUNT(Emp_ID) AS "Num Rentals"
FROM Employee JOIN rental USING (Emp_ID)
        JOIN store USING (Store_Num)
GROUP BY Emp_ID, First_Name, Last_Name, Store_Num, Store_Name;

Emp. Name            Store                Num Rentals
-------------------- -------------------- -----------
104: Victoria Lee    20: SQ Hill's DVD              4
102: Steve Baker     10: Oakland's DVD              3
103: Lily Evans      10: Oakland's DVD              3
105: Gloria Tremblay 20: SQ Hill's DVD              4
101: Jim Smith       10: Oakland's DVD              5
106: Emma Wesley     30: DVD Hub                    3

6 rows selected.

This gets the number of rentals each employee processed and what store they work at. I only want to get the top employee at each store. The result table should be...

Emp. Name            Store                Num Rentals
-------------------- -------------------- -----------
104: Victoria Lee    20: SQ Hill's DVD              4
105: Gloria Tremblay 20: SQ Hill's DVD              4
101: Jim Smith       10: Oakland's DVD              5
106: Emma Wesley     30: DVD Hub                    3

Thank you for all of your help in advance!

Upvotes: 0

Views: 57

Answers (2)

Multisync
Multisync

Reputation: 8787

You can use analytic function RANK:

select "Emp. Name", "Store", "Num Rentals" FROM
    (SELECT Emp_ID || ': ' || First_Name || ' ' || Last_Name AS "Emp. Name",
            Store_Num || ': ' || Store_Name AS "Store",
            COUNT(Emp_ID) AS "Num Rentals",
            rank() over(partition by Store_Num, Store_Name order by COUNT(Emp_ID) desc) rw
    FROM Employee JOIN rental USING (Emp_ID)
            JOIN store USING (Store_Num)
    GROUP BY Emp_ID, First_Name, Last_Name, Store_Num, Store_Name)
WHERE rw = 1;

RANK calculates a rank of each row accoding to ORDER BY expr (several row can have the same rank) for each group expressed in PARTITION BY.

Analytic functions are calculated after GROUP BY

Upvotes: 4

Dmitriy
Dmitriy

Reputation: 5565

select t."Store", max("Emp. Name") keep (dense_rank first order by "Num Rentals" desc)
  from (SELECT Emp_ID || ': ' || First_Name || ' ' || Last_Name AS "Emp. Name",
            Store_Num || ': ' || Store_Name AS "Store",
            COUNT(Emp_ID) AS "Num Rentals"
    FROM Employee JOIN rental USING (Emp_ID)
            JOIN store USING (Store_Num)
    GROUP BY Emp_ID, First_Name, Last_Name, Store_Num, Store_Name) t;

Upvotes: 0

Related Questions