Calculation in WHERE clause

   
/*
mysql> Drop table Item;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Item
    -> (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder SMALLINT UNSIGNED NOT NULL,
    ->    Reserved SMALLINT UNSIGNED NOT NULL,
    ->    Department ENUM('Classical', 'Popular') NOT NULL,
    ->    Category VARCHAR(20) NOT NULL,
    ->    RowUpdate TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)

    ->           VALUES ('Bloodshot',      10,      5,       1, 'Popular',
'Rock'),
    ->                  ('Most',           10,      5,       2, 'Classical',
'Opera'),
    ->                  ('Jazz',           17,      4,       3, 'Popular',
'Jazz'),
    ->                  ('Class',           9,      4,       4, 'Classical',
'Dance'),
    ->                  ('Violin',         24,      2,       5, 'Classical',
'General'),
    ->                  ('Cha Cha',        16,      6,       6, 'Classical',
'Vocal'),
    ->                  ('Blues',           2,     25,       7, 'Popular',
'Blues'),
    ->                  ('Pure',           32,      3,      18, 'Popular',
'Jazz'),
    ->                  ('Mud',            12,     15,      19, 'Popular',
'Country'),
    ->                  ('The',             5,     20,      11, 'Popular',
'New Age'),
    ->                  ('Embrace',        24,     11,      12, 'Popular',
'New Age'),
    ->                  ('Magic',          42,     17,      13, 'Classical',
'General'),
    ->                  ('Lake',           25,     44,      24, 'Classical',
'Dance'),
    ->                  ('LaLala',         20,     10,       5, 'Classical',
'Opera'),
    ->                  ('Soul',           15,     30,      16, 'Popular',
'Blues'),
    ->                  ('Stages',         42,      0,       7, 'Popular',
'Blues'),
    ->                  ('Six',            16,      8,       6, 'Classical',
'General');
Query OK, 17 rows affected (0.01 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql> select * from Item;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
|  1 | Bloodshot |      10 |       5 |        1 | Popular    | Rock     | 2005-10-09 09:19:49 |
|  2 | Most      |      10 |       5 |        2 | Classical  | Opera    | 2005-10-09 09:19:49 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  4 | Class     |       9 |       4 |        4 | Classical  | Dance    | 2005-10-09 09:19:49 |
|  5 | Violin    |      24 |       2 |        5 | Classical  | General  | 2005-10-09 09:19:49 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  | Vocal    | 2005-10-09 09:19:49 |
|  7 | Blues     |       2 |      25 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
|  8 | Pure      |      32 |       3 |       18 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  9 | Mud       |      12 |      15 |       19 | Popular    | Country  | 2005-10-09 09:19:49 |
| 10 | The       |       5 |      20 |       11 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 12 | Magic     |      42 |      17 |       13 | Classical  | General  | 2005-10-09 09:19:49 |
| 13 | Lake      |      25 |      44 |       24 | Classical  | Dance    | 2005-10-09 09:19:49 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  | Opera    | 2005-10-09 09:19:49 |
| 15 | Soul      |      15 |      30 |       16 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 16 | Stages    |      42 |       0 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 17 | Six       |      16 |       8 |        6 | Classical  | General  | 2005-10-09 09:19:49 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)

mysql> SELECT Department, Category, Name
    -> FROM Item
    -> WHERE (InStock+OnOrder-Reserved)<15
    -> ORDER BY Department DESC, Category ASC;
+------------+----------+-----------+
| Department | Category | Name      |
+------------+----------+-----------+
| Popular    | Country  | Mud       |
| Popular    | New Age  | The       |
| Popular    | Rock     | Bloodshot |
| Classical  | Dance    | Class     |
| Classical  | Opera    | Most      |
+------------+----------+-----------+
5 rows in set (0.00 sec)
*/

Drop table Item;

CREATE TABLE Item
(
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM('Classical', 'Popular') NOT NULL,
   Category VARCHAR(20) NOT NULL,
   RowUpdate TIMESTAMP NOT NULL
);


INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)
          VALUES ('Bloodshot',      10,      5,       1, 'Popular',      'Rock'),
                 ('Most',           10,      5,       2, 'Classical',    'Opera'),
                 ('Jazz',           17,      4,       3, 'Popular',      'Jazz'),
                 ('Class',           9,      4,       4, 'Classical',    'Dance'),
                 ('Violin',         24,      2,       5, 'Classical',    'General'),
                 ('Cha Cha',        16,      6,       6, 'Classical',    'Vocal'),
                 ('Blues',           2,     25,       7, 'Popular',      'Blues'),
                 ('Pure',           32,      3,      18, 'Popular',      'Jazz'),
                 ('Mud',            12,     15,      19, 'Popular',      'Country'),
                 ('The',             5,     20,      11, 'Popular',      'New Age'),
                 ('Embrace',        24,     11,      12, 'Popular',      'New Age'),
                 ('Magic',          42,     17,      13, 'Classical',    'General'),
                 ('Lake',           25,     44,      24, 'Classical',    'Dance'),
                 ('LaLala',         20,     10,       5, 'Classical',    'Opera'),
                 ('Soul',           15,     30,      16, 'Popular',      'Blues'),
                 ('Stages',         42,      0,       7, 'Popular',      'Blues'),
                 ('Six',            16,      8,       6, 'Classical',    'General');

select * from Item;

SELECT Department, Category, Name
FROM Item
WHERE (InStock+OnOrder-Reserved)<15
ORDER BY Department DESC, Category ASC;

Related examples in the same category

1.	Not equal in where
2.	Getting the List of Products That Are on Catalog Promotion
3.	Using Where Conditions
4.	WHERE Clause Comparisons
5.	Combining WHERE Conditions
6.	Compare and calculate in Where clause
7.	Do Calculation in Where and order
8.	Where clause: nested conditions
9.	Where clause: calculation and equal condition
10.	Where clause: compare
11.	Where clause: XOR
12.	Use where clause the narrow down results
13.	Use CURRENT_DATE in where clause
14.	WHERE TRUE OR FALSE
15.	Change localhost to the name of the machine where you'll be working.
16.	WHERE clauses can test multiple conditions.
17.	To put first those records where people sent messages to themselves
18.	Using a WHERE clause that matches up values in the author ID column
19.	Add a WHERE clause that looks for NULL values in the book column that is named in the ON clause
20.	Add an expression to the WHERE clause that explicitly excludes the reference
21.	Adds an additional condition to the WHERE clause and the calculated columns must have a total greater than 20
22.	A WHERE clause that contains two expressions (conditions)
23.	Searches for surnames that have an e anywhere in the name and then end with an e:
24.	Show all records where the color is "Silver" and the price is above 100.00
25.	Show records where make is "Krups", "Gaggia" or "DeLonghi" and the model is not "TSK-182" or "EC410"
26.	Show all records where the color is not "Silver" or the price is below 100.00
27.	Get the number of items for each color where the price exceeds 150.00 and when there is more than 1 item for t
28.	Check day name in where clause
29.	Compare the results from two statements

Calculation in WHERE clause : Where « Where Clause « SQL / MySQL

Related examples in the same category