增加查询max(count)和min(count)

Question

我正试图将max和min添加到下面的查询:

SELECT category, COUNT(distinct VIN) COUNT
FROM stock
group by category
order by category ASC

期望的结果是显示计数中项目最多的类别和计数中项目最少的类别。

当前输出:

CATEGORY COUNT
Sedan    25
SUV      15
TRUCK    15
AWD      10

所需输出:

CATEGORY COUNT
Sedan    25
AWD      10

我想显示的是最大类别数在这个例子中是SEDAN最小类别数是AWD

我相信这将需要一个HAVING语句与嵌套子查询，任何帮助将是非常感激的

Oracle 11g

Answer 1

select count(VIN), Category 
from STOCK
having count(VIN)=(select max(count(VIN)) from STOCK group by Category )
OR count(VIN)=(select min(count(VIN))  from STOCK group by Category )
group by Category; 

Answer 2

我能想到的最好的方法是将两个查询联合起来查找最大和最小计数，如下所示:

Select category, CNT
FROM
(SELECT category, COUNT(distinct VIN) CNT
FROM stock
group by category
Order by CNT asc) a
where RowNum = 1
UNION
Select category, CNT
FROM
(SELECT category, COUNT(distinct VIN) CNT
FROM stock
group by category
Order by CNT desc) a
where RowNum = 1

SQL Fiddle: http://www.sqlfiddle.com/#!4/555515/18/0

Answer 3

select 
   count(VIN), 
   Category 
from STOCK
having count(VIN)=
    (
        select max(count(VIN))  
        from STOCK group by project_status_id 
    )
    OR count(project_status_id)=
    (
        select min(count(project_status_id))  
        from project_status group by project_status_id 
     )
group by project_status_id

Answer 4

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE counts ( CATEGORY, "COUNT" ) AS
          SELECT 'Sedan',    25 FROM DUAL
UNION ALL SELECT 'SUV',      15 FROM DUAL
UNION ALL SELECT 'TRUCK',    15 FROM DUAL
UNION ALL SELECT 'AWD',      10 FROM DUAL;

查询1

:

SELECT MAX( CATEGORY ) KEEP ( DENSE_RANK LAST ORDER BY "COUNT" ASC ) AS CATEGORY,
       MAX( "COUNT" ) AS "COUNT"
FROM   COUNTS
UNION ALL
SELECT MIN( CATEGORY ) KEEP ( DENSE_RANK FIRST ORDER BY "COUNT" ASC ) AS CATEGORY,
       MIN( "COUNT" ) AS "COUNT"
FROM   COUNTS

结果

:

| CATEGORY | COUNT |
|----------|-------|
|    Sedan |    25 |
|      AWD |    10 |