select max from a count in sql-CodePudding

Is there any way I can select the row with the max value of a count? I have

SELECT
    "Month",
    COUNT( Trip_Id ) AS 'Number of Trips'
FROM
    Tor_Bikes AS tb 
GROUP BY
    "Month";

which gives me a table of each month with its number of trips for that month. I need to find the month that has the most number of trips. How do I do this?

CodePudding user response：

You should use subqueries to achieve that:

SELECT MAX(Number_of_Trips) FROM (SELECT "Month", Count( Trip_Id ) FROM
    Tor_Bikes AS tb 
GROUP BY
    "Month";)

CodePudding user response：

I'm learning SQL myself, so this is by no means canonical.

We all know how to produce such a table:

(SELECT `productID`, count(`productID`) AS `TotalPriceChanges` FROM `ProductCostHistory` GROUP BY `productID`) 

ProductID       Count(*)
    '707'     | '3'
    '708'     | '3'
    '709'     | '1'
    '710'     | '1'
    '711'     | '3'

If I'm understanding correctly, you want to select the Max value of THIS table. Well, you can simply use the above as a derived table - sub query or CTE (Common Table Expression). Using MYSQL it would be something like this:

Select ProductID, max(TotalPriceChanges) FROM
          (SELECT `productID`, count(`productID`) AS `TotalPriceChanges` FROM `ProductCostHistory` GROUP BY `productID`) as derivedTable
Group by `ProductID`

We use an alias as to name the derived table, and instead of using the aggregative function count we use the function max. The above works for me.

Final Solution

SELECT Month, Max('Number of Trips') FROM
    (
        SELECT
        "Month",
        COUNT( Trip_Id ) AS 'Number of Trips'
    FROM
        Tor_Bikes AS tb 
    GROUP BY
        "Month"  AS DerivedTable;
    )
GROUP BY MONTH

CodePudding user response：

Here is the query you are searching for:

SELECT      Month, Max('Number of Trips') 
FROM
    (
      SELECT    Month, COUNT( Trip_Id ) AS 'Number of Trips'
      FROM      Trips AS tb 
      GROUP BY  Month
    )

CodePudding user response：

I'm new to SQL, correct me if i'm wrong but i think the order by function could give the same result... just with other unwanted results but at least its what you see first :)

Order by the count of Trip ID in descending order after grouping by month..