SELECT MAX of COUNT

Question

I have a table "well". It contains a column app_rate_unit (type: nvarchar). My goal is to count every distinct value in the table and let the DBMS (MS Server 2005) give me the most occurring one.

This is my code:

SELECT MAX(app_rate_unit) AS MAX_APP
  FROM (SELECT app_rate_unit, COUNT(*) AS co
          FROM dbo.well AS w
         GROUP BY app_rate_unit
        ) AS derivedtbl_1

The poblem with it is however, that my DBMS actually delivers the lowest count to me.

SideQuestion: How do I filter for a foreign key (in the table) and NOT NULL (in app_rate_unit) when counting?

Solution 1

select top 1 app_rate_unit, count(*) from dbo.well
group by app_rate_unit
order by count(*) desc

Solution 2

Try this

    SELECT 
        COUNT(app_rate_unit)AS MAX_APP , 
        app_rate_unit 
    FROM 
        dbo.well
    WHERE
            app_rate_unit IS NOT NULL
    GROUP BY 
        app_rate_unit 
    ORDER BY 
            MAX_APP DESC

The above script will give you the count and the item. You can change the count if you are not sure only one item will have the maximum number of occurrence.