SQL GROUP BY and HAVING in examples

Here we show you the usage HAVING and GROUP BY. As an advance, we have an example with GROUP BY multiple columns.
In first section you have got the data schema. Later you have some tasks on it. After that you have the solutions for the tasks.



Tables schema
; dbn test2
DROP TABLE customers;
DROP TABLE orders;

CREATE TABLE customers (
    CustomerID INTEGER PRIMARY KEY AUTO_INCREMENT,
    Name CHAR(50),
    Address CHAR(50)
);

CREATE TABLE orders (
    OrderID INTEGER PRIMARY KEY AUTO_INCREMENT,
    OrderDate DATE,
    CustomerID INTEGER REFERENCES customers( CustomerID ),
    OrderPrice DOUBLE
);


INSERT INTO customers(Name, Address) VALUES ("Nadejda",  "Bulgaria, Sofia, ...");
INSERT INTO customers(Name, Address) VALUES ("Katerina", "Bulgaria, Sofia, ...");
INSERT INTO customers(Name, Address) VALUES ("Maria",    "Bulgaria, Sofia, ...");

INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/12", 1, 1000 );
INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/12", 2, 1600 );
INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/12", 1,  700 );
INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/15", 1,  300 );
INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/15", 3, 2000 );
INSERT INTO orders( OrderDate, CustomerID, OrderPrice) VALUES ( "2008/11/17", 2,  100 );


Tasks:

• Show the amount of money, every user has spend in orders;
• Show the amount of money, every user has spend in orders per day;
• Show users, which have amount of money in orders less then 2000

SELECT * FROM orders;
+---------+------------+------------+------------+
| OrderID | OrderDate  | CustomerID | OrderPrice |
+---------+------------+------------+------------+
|       1 | 2008-11-12 |          1 |       1000 |
|       2 | 2008-11-12 |          2 |       1600 |
|       3 | 2008-11-12 |          1 |        700 |
|       4 | 2008-11-15 |          1 |        300 |
|       5 | 2008-11-15 |          3 |       2000 |
|       6 | 2008-11-17 |          2 |        100 |
+---------+------------+------------+------------+
6 rows in set (0.00 sec)

SELECT CustomerID,SUM(OrderPrice) FROM orders
GROUP BY CustomerID;
+------------+-----------------+
| CustomerID | SUM(OrderPrice) |
+------------+-----------------+
|          1 |            2000 |
|          2 |            1700 |
|          3 |            2000 |
+------------+-----------------+
3 rows in set (0.00 sec)

; extract the sum of orders for each person for every single day
; hint: multiple group by

SELECT CustomerID, OrderDate, SUM(OrderPrice) FROM orders
GROUP BY CustomerID, OrderDate;
+------------+------------+-----------------+
| CustomerID | OrderDate  | SUM(OrderPrice) |
+------------+------------+-----------------+
|          1 | 2008-11-12 |            1700 |
|          1 | 2008-11-15 |             300 |
|          2 | 2008-11-12 |            1600 |
|          2 | 2008-11-17 |             100 |
|          3 | 2008-11-15 |            2000 |
+------------+------------+-----------------+
5 rows in set (0.00 sec)

SELECT CustomerID, SUM(OrderPrice) FROM orders
GROUP BY CustomerID
HAVING SUM(OrderPrice)<2000;

+------------+-----------------+
| CustomerID | SUM(OrderPrice) |
+------------+-----------------+
|          2 |            1700 |
+------------+-----------------+
1 row in set (0.00 sec)