CODEWITHSUNDEEP
sqloracle11gmaxmin
ITworldR
ITworldR

Reputation: 65

Adding max(count) and min(count) to query

I am trying to add max and min to the query below:

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

The desired results are to show the category with the most items in the count and to show the category with the least items in the count.

CURRENT OUTPUT:

CATEGORY COUNT
Sedan    25
SUV      15
TRUCK    15
AWD      10

DESIRED OUTPUT:

CATEGORY COUNT
Sedan    25
AWD      10

I basically want to show the maximum count which in this example would be SEDAN and the minimum category count which would be AWD

I believe this would require a HAVING statement with a nested sub-query, any help would be much appreicated

I am using Oracle 11g

Upvotes: 1

Views: 12733

Answers (4)

MT0
MT0

Reputation: 167962

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;

Query 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

Results:

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

Upvotes: 0

Nikhil
Nikhil

Reputation: 42

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;

Upvotes: 2

Christian Barron
Christian Barron

Reputation: 2755

The best way I can think of doing this is to union two queries to find the max and min counts like below:

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

Upvotes: 1

Nikhil
Nikhil

Reputation: 42

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

Upvotes: 0

Related Questions