Unlocking Data Insights: GROUP BY for Categorization and DISTINCT for Unique Values in SQL

Here's a table summarizing the key differences:

Example:

Imagine a table storing customer orders with columns for customer_id and product_name. You want to find the total number of unique products purchased by each customer.

• Using GROUP BY:

SELECT customer_id, COUNT(DISTINCT product_name) AS total_products
FROM orders
GROUP BY customer_id;

This query groups orders by customer_id and then uses COUNT with DISTINCT to count the unique product names within each customer group.

• Using DISTINCT (incorrect approach):

SELECT DISTINCT customer_id, product_name
FROM orders;

This wouldn't achieve the desired outcome. It would only return unique combinations of customer_id and product_name, not necessarily providing the total number of unique products per customer.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

This code assumes you have a table named employees with a column named department. It groups the employees by department and uses the COUNT(*) function to calculate the number of employees in each department.

Example 2: Listing all distinct city names from a customer table (DISTINCT)

SELECT DISTINCT city
FROM customers;

This code assumes you have a table named customers with a column named city. It uses the DISTINCT keyword to remove duplicate city names and returns a list of unique cities.

Example 3: Combining GROUP BY and aggregation with DISTINCT (finding customers who bought distinct colors)

SELECT customer_id, COUNT(DISTINCT color) AS distinct_colors_bought
FROM orders
GROUP BY customer_id;

• UNION ALL: This can be used in some cases as an alternative to removing duplicates with DISTINCT. It combines the results of two or more queries, eliminating duplicates only if they appear in all the selected queries.

Example (Finding all customers and all employees, keeping duplicates):

SELECT * FROM customers
UNION ALL
SELECT * FROM employees;

EXISTS Clause (Checking for existence within groups):

• This can be a substitute for GROUP BY in specific scenarios. It allows you to check if a certain condition exists within a group defined by a subquery.

Example (Finding customers who have placed more than one order):

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
  HAVING COUNT(*) > 1
);

Window Functions (Advanced grouping and filtering):

• Functions like ROW_NUMBER() or NTILE() can be used for more complex grouping and filtering tasks. They allow you to assign a position or rank to each row within a result set based on specific criteria.

Example (Finding the top 3 products purchased by each customer):

SELECT customer_id, product_name
FROM (
  SELECT customer_id, product_name, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY quantity DESC) AS row_num
  FROM orders
) ranked_orders
WHERE row_num <= 3;

JOINs with aggregation (Grouping based on relationships):

• Joining tables with aggregation can sometimes achieve similar results as GROUP BY, especially when dealing with related data across multiple tables.

Example (Finding the total quantity of each product sold by category):

SELECT p.category, SUM(o.quantity) AS total_sold
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.category;