HAVING clause without Aggregate Functions

USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'

Here is the result set:

type
------------------
popular_comp
psychology

(2 row(s) affected)

When multiple conditions are included in HAVING, they are combined with AND, OR, or NOT. The following example shows how to group titles by publisher, including only those publishers with identification numbers greater than 0800, who have paid more than $15,000 in total advances, and who sell books for an average of less than $20.

SELECT pub_id, SUM(advance) AS AmountAdvanced,
       AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
GROUP BY pub_id
HAVING SUM(advance) > $15000
   AND AVG(price) < $20

ORDER BY can be used to order the output of a GROUP BY clause. This example shows using the ORDER BY clause to define the order in which the rows from a GROUP BY clause are returned:

SELECT pub_id, SUM(advance) AS AmountAdvanced,
       AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
  AND price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
   AND AVG(price) < $20
ORDER BY pub_id DESC