CODEWITHSUNDEEP
mysqlsqlwhere-clausemin
mojo
mojo

Reputation: 59

how to use min as a condition in sql query

cat use min as condition

the where statement is where it breaks but i cant fix it

select category, count(*) as number_of_cats
from books
where number_of_cats > min(number_of_cats)
group by category
order by category;

Upvotes: 3

Views: 417

Answers (2)

DDS
DDS

Reputation: 2479

Having + sub-query

select category, count(*) as number_of_books
from books
group by category
having count(*) >     -- check the one whose count is STRICTLY greater then minimum
                      (  select min (st.t) -- find the minimum of all categories
                         from
                         ( select count(*) as t --find the count for all categories
                           from books
                           group by category
                                    ) st -- an alias to avoid parsing errors
                          )

Another option, but with this solution in case of ex-aequo only first category is removed:

select select category, count(*) as number_of_books
    from books
    where category not in (select bb.category 
                           from books bb
                           group by bb.category 
                           order by count(*) asc
                           limit 1)
    group by category

Upvotes: 3

Richard Hansell
Richard Hansell

Reputation: 5403

You could use common table expressions here, e.g.:

WITH CategoryCount AS (
    SELECT 
        category, 
        COUNT(*) AS number_of_books
    FROM
        books
    GROUP BY 
        category),
MinBooks AS (
    SELECT
        MIN(number_of_books) AS min_number_of_books
    FROM
        CategoryCount)
SELECT
    cc.*
FROM
    CategoryCount cc
    CROSS JOIN MinBooks m
WHERE
    cc.number_of_books > m.min_number_of_books;

Upvotes: 1

Related Questions