Flowers > 5

     

Write an SQL query to fetch all the flowers that are being delivered to at-least 5 houses from the table.

Input :-

Flowers table :-
House is the Primary Key column for the Flowers table.

House	Flowers
A	Lily
B	Rose
C	Lily
D	Lily
E	Rose
F	Lily
G	Sun-Flower
H	Jasmine
I	Lily
J	Tulip
K	Lily

Output :-

Flowers

Lily

Query :-

select flowers
from Flowers
group by flowers
having count(flowers)>=5
order by count(flowers) desc;

Explanation : - 

The query retrieves a list of flowers from a table called "Flowers." 

The select  part of the query specifies that we want to select the column named ‘flowers’ from the table "Flowers”. 

The GROUP BY clause is used to group the rows in the table based on the values in the ‘flowers’ column. In this case, it groups the flowers with the same name together.

The HAVING clause is used to filter the grouped results based on a condition. In this case, it checks the count of flowers in each group and only includes the groups where the count is greater than or equal to 5. This ensures that we only get flowers that appear at least five times from the table.

The ORDER BY clause is used to sort the results based on a specified column or expression. In this case, it sorts the grouped results based on the count of flowers in each group in descending order (DESC), meaning the flowers with the highest count will appear first in the result.

To summarise, the query retrieves the names of flowers that appear at least five times in the "Flowers" table. The result will display the flowers with the highest counts first.