Use of Having Clause and case in SQL

HAVING is very resemble WHERE. The only dissimilarity is that WHERE filters rows and HAVING filters groups. Increase in SQL HAVING clause because the WHERE keyword can not be used with the combination of functions.

Syntax

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Table name Customers

SELECT COUNT(CustomerId), City
FROM Customers
GROUP BY City
HAVING COUNT(CustomerId) > 2;

SELECT COUNT(CustomerId), City
FROM Customers
GROUP BY City
HAVING COUNT(CustomerId) > 5
ORDER BY COUNT(CustomerId) Asc;

Grouping and Sorting by using the Having clause

 You need to use ORDER BY for sorting the output of GROUP BY.

The HAVING clause refines the data so that it can return more than two items to Customers. Finally, the output is sorted using the Customers BY clause.

SELECT Customerid, COUNT(*) AS Customers
FROM Customers
GROUP BY City
HAVING COUNT(Customerid) >= 2;

The CASE statement operates direct on conditions and returns a value when the first condition is assembled (like an if-then-else statement). So, once a condition is true, it will hold back (stop) reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

if there are no conditions that are true or no ELSE part is true it will return a NULL.

WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Table name Customers

Example

SELECT Name, City, Postalcode
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Postalcode
    ELSE City
END);