Русский    English

HAVING clause

Let's determine only red squares, i.e. squares which were painted with only red spray cans, and total quantity of red-color paint equals 255 on each of these squares. We consider Painting database.

This problem can be solved by a number of ways. Here we'll use CASE operator in HAVING clause.

Here is idea. Let's group the paintings by square id while summing quantity of paint. In so doing red-color paint will be added to the sum with sign "+" whereas other paints - with sign "-". In view of the fact that the volume of any paint must not exceed 255, the result that equals 255 exactly says to us that all paint is of red color and no other color paint was used.

Here is the solution:

  1. SELECT b_q_id
  2. FROM utb JOIN utv ON v_id=b_v_id
  3. GROUP BY b_q_id
  4. HAVING SUM(CASE WHEN v_color='R' THEN b_vol ELSE -b_vol END) = 255;

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100