CODEWITHSUNDEEP
sqloracle-databasefunctioncountwhere-clause
Mila
Mila

Reputation: 21

Count function in Oracle

The question I'm struggling with is this:

I have a list of helicopter names in different charters and I need to find out WHICH helicopter has the least amount of charters booked. Once I find that out I need to ONLY display the one that has the least.

I so far have this:

SELECT Helicopter_Name 
       , COUNT (Distinct Charter_NUM)
FROM Charter_Table
GROUP BY Helicopter Name

This is where I am stuck. I realise MIN could be used to pick out the value that is the smallest but I am not sure how to integrate this into the command.

Something like Where MIN = MIN Value

I'd really appreciate it.

Upvotes: 0

Views: 1773

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562328

Here's a solution that just takes the first row, after ordering them lowest count to highest count:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Charter_Count) AS RN
    FROM (
        SELECT Helicopter_Name, COUNT(Distinct Charter_NUM) Charter_Count
        FROM Charter_Table GROUP BY Helicopter_Name
    ) AS t1
) AS t2
WHERE t2.RN = 1;

Here's a solution that uses HAVING to compare the count of a given group to the lowest count given all the groups. Based on answer given by @Gary but adding another level of subquery.

SELECT Helicopter_Name, COUNT(Distinct Charter_NUM) AS Charter_Count
FROM Charter_Table 
GROUP BY Helicopter_Name
HAVING COUNT(Distinct Charter_NUM) = (SELECT MIN(C) FROM
    (SELECT COUNT(Distinct Charter_NUM) AS C
     FROM Charter_Table 
     GROUP BY Helicopter_Name))

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

One thing you need to consider is, what happens if two or more helicopters have the least number of charters booked?

If you just want to "pick one", you can simply sort by the count (descending) and report the first result:

SELECT * FROM (
   SELECT Helicopter_Name, c FROM (
      SELECT Helicopter_Name, COUNT (Distinct Charter_NUM) AS c
      FROM Charter_Table GROUP BY Helicopter 
   ) ORDER BY c DESC
) WHERE ROWNUM = 1;

Alternatively, if you need to report all the helis that have the least number of charters taking ties into account, you can use the RANK analytic function:

SELECT Helicopter_Name, c FROM (
   SELECT Helicopter_Name, c, RANK() OVER (ORDER BY c) therank FROM (
      SELECT Helicopter_Name, COUNT (Distinct Charter_NUM) AS c
      FROM Charter_Table GROUP BY Helicopter 
   )
) WHERE therank = 1;

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

This is the simple answer. 

SELECT Helicopter_Name
FROM Charter_Table 
GROUP BY Helicopter_Name
HAVING COUNT (Distinct Charter_NUM) = 
   (SELECT MIN(COUNT (Distinct Charter_NUM)) CNT
   FROM Charter_Table 
   GROUP BY Helicopter_Name)

Upvotes: 0

Mila
Mila

Reputation: 21

Oh just thought of something could i add this:

(for example just say theres 10 charters and helicopter RED had the least)

SELECT Helicopter_Name COUNT (Distinct Charter_NUM) FROM Charter_Table GROUP BY Helicopter Name HAVING Total = MIN

Would this bring forward Helicopter RED's value?

Upvotes: 0

Related Questions