Using Group By and Having to Generate Summary

Group By clause is used to group the returned result set into groups based on grouping columns. Group By clause mostly uses with aggregate functions to generate summary information. The common syntax of using Group By clause is as follows:

SELECT col1, aggregate_func(col2)
FROM table_name
GROUP BY col1

As an example, if you want to find out number of order each customer ordered you can use GROUP BY clause to do so. Here is the query:

SELECT customer_id, 
       COUNT(orderinfo_id) AS "number of order"
FROM orderinfo
GROUP BY customer_id

We retrieved the customer information base on the customer_id. We use COUNT as aggregate function COUNT to count number of order based on orderinfo_id and then we use customer_id as grouping column. This is the output of the query:

customer_id	number of order
13	1
15	1
8	2
3	1

What if you want to know only customer which has more than one order? in this case you can use HAVING clause to limit the groups based on conditions. Here is the common syntax of usages of GROUP BY and HAVING clauses:

SELECT col1, aggregate_func(col2)
FROM table_name
GROUP BY col1
HAVING conditions

Here is the query to find customer who has more than one order:

SELECT customer_id, 
       COUNT(orderinfo_id) AS "number of order"
FROM orderinfo
GROUP BY customer_id
HAVING COUNT(orderinfo_id) > 1

Here is the output of the query:

customer_id	number of order
8	2

• « Previous page
• Next page »