SELECT MAX of COUNT

问题:

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?

回答1:

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

回答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.

回答3:

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

回答4:

In PostgreSQL we can write query which using max of count as

select max(count) from (

select count(id) from Table _name group by created_by,status_id having status_id = 6 ) as Alias

eg

select max(count) from (

select count(id) from orders group by created_by,status_id having status_id = 6 ) as foo