Get the order number and number of items ordered where the color is not Pink and the number of items ordered i : Sort Order « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Sort Order

Get the order number and number of items ordered where the color is not Pink and the number of items ordered i

       
s fewer than 3 sorted by order number
mysql>
mysql> CREATE TABLE IF NOT EXISTS tub
    -> (
    ->   id     INT             AUTO_INCREMENT  PRIMARY KEY,
    ->   num    INT             NOT NULL,
    ->   ref    VARCHAR(10)     NOT NULL,
    ->   qty    INT             DEFAULT 1,
    ->   col    CHAR(10)        NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # insert 10 records into the "tub" table
mysql> INSERT INTO tub (num, ref, col) VALUES (8004, 101, "Red");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8004, 103, "Lime");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8004, 104, "Blue");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8003, 104, "Blue");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8002, 105, "Red");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8002, 102, "Lime");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8002, 103, "Pink");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8001, 104, "Red");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8001, 105, "Lime");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tub (num, ref, col) VALUES (8004, 102, "Blue");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # display all data in the "tub" table
mysql> SELECT * FROM tub;
+----+------+-----+------+------+
| id | num  | ref | qty  | col  |
+----+------+-----+------+------+
|  1 | 8004 | 101 |    1 | Red  |
|  2 | 8004 | 103 |    1 | Lime |
|  3 | 8004 | 104 |    1 | Blue |
|  4 | 8003 | 104 |    1 | Blue |
|  5 | 8002 | 105 |    1 | Red  |
|  6 | 8002 | 102 |    1 | Lime |
|  7 | 8002 | 103 |    1 | Pink |
|  8 | 8001 | 104 |    1 | Red  |
|  9 | 8001 | 105 |    1 | Lime |
| 10 | 8004 | 102 |    1 | Blue |
+----+------+-----+------+------+
10 rows in set (0.00 sec)

mysql>
mysql> SELECT num, COUNT(*) AS num_items
    -> FROM tub
    -> WHERE col != "Pink"
    -> GROUP BY num
    -> HAVING COUNT(*) < 3
    -> ORDER BY num;
+------+-----------+
| num  | num_items |
+------+-----------+
| 8001 |         2 |
| 8002 |         2 |
| 8003 |         1 |
+------+-----------+
3 rows in set (0.00 sec)

mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS tub;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

   
    
    
    
    
    
    
  

Related examples in the same category