SQL Basics

• Suppose we want to know how many different products each vendor (vend_id) can supply.

SELECT vend_id,
       COUNT(*) AS AantalProducten
FROM   Products;

Error Code: 1140. In aggregated query without GROUP BY, expression

• This query combines incompatible result levels: it would return several vend_id values but only one total row count. This does not work!
• Use GROUP BY to combine aggregate functions with fields that return several values
• GROUP BY combines rows into groups that can be evaluated together
• It lets you apply aggregate functions to a subset of rows

SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
GROUP BY vend_id;

• A GROUP BY clause can contain multiple columns
• As a rule, include every non-aggregated SELECT column in GROUP BY
• Whether aliases are allowed in GROUP BY depends on the DBMS

Select prod_id and the total quantity ordered for each product from OrderItems

-- Oracle / MySQL / SQL Server
SELECT   prod_id,
         SUM(quantity) AS TotaalBesteld	
FROM     OrderItems
GROUP BY prod_id;

• WHERE does not filter the groups produced by GROUP BY

• Use HAVING to filter groups
• WHERE and HAVING work in similar ways, but:
  • WHERE filters rows
  • HAVING filters groups

SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
GROUP BY vend_id
HAVING   COUNT(*) < 4;

Select the customer IDs of customers who have placed two or more orders

-- Oracle / MySQL / SQL Server
SELECT   cust_id,
         COUNT(*) AS AantalOrders
FROM     Orders
GROUP BY cust_id
HAVING   COUNT(*) >= 2;

• Sometimes a WHERE filter and a HAVING filter are both needed

Select all vendors with at least two products priced at 4 or more

-- Oracle / MySQL / SQL Server
SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
WHERE    prod_price >= 4
GROUP BY vend_id
HAVING   COUNT(*) >= 2;

• WHERE is evaluated before grouping
• HAVING is evaluated after grouping

• Differences and similarities between ORDER BY and GROUP BY:

A GROUP BY result may appear to be sorted, but its order is not guaranteed.
Add ORDER BY when you need a particular order. Never rely on the DBMS's GROUP BY behavior for sorting

41. Show the number of vendors per country (3)

42. Show the quantity ordered for each product (7)

43. Show the vendors whose combined product prices in Products exceed 20 (1)

44. Show each order number and its total price, provided that the total exceeds 1000 (3)

45. Show each order number and the total price of its first two order lines (order_item), provided that this total exceeds 1000. (2)