GROUP BY/ORDER BY

Question 1

Definition & Use

Answer 1

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Question 2

Syntax

Answer 2

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Question 3

SQL GROUP BY Example

Answer 3

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Question 4

SQL GROUP BY Clause With JOIN

Answer 4

– join the Customers and Orders tables
– select customer_id and first_name from Customers table
– also select the count of order ids from Orders table
– group the result by customer_id

SELECT Customers.customer_id, Customers.first_name,
Count(Orders.order_id) AS order_count
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_id;

Question 5

GROUP BY With Multiple Columns

Answer 5

– group by country and state
–to calculate minimum age of each group

SELECT country, state, MIN(age) AS min_age
FROM Persons
GROUP BY country, state;

*The SQL command groups all persons with similar country and state, and gives the minimum age of each group.

Question 6

GROUP BY With HAVING Clause

Answer 6

We can use the GROUP BY clause with the HAVING clause to filter the result set based on aggregate functions. For example,

– select the customer_id count and country column from Customers
– group by country if the count is greater than 1

SELECT COUNT(customer_id), country
FROM Customers
GROUP BY country
HAVING COUNT(customer_id) > 1;
Here, the SQL command:

counts the number of rows by grouping them by country
returns the result set if their count is greater than 1.