1-5.できる

1.GROUP BY-条件に従って統計値を取得します.

SELECT Country FROM Customers
GROUP BY Country;

SELECT CategoryID FROM Products
GROUP BY CategoryID;

💡 複数のカラムに基づいてグループ化することもできます.

SELECT 
  Country, City,
  CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;

📚 グループ関数の使用

SELECT
  COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;

SELECT
  ProductID,
  SUM(Quantity) AS QuantitySum
FROM OrderDetails
GROUP BY ProductID
ORDER BY QuantitySum DESC;

SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
GROUP BY CategoryID;

SELECT 
  CONCAT_WS(', ', City, Country) AS Location,
  COUNT(CustomerID)
FROM Customers
GROUP BY Country, City;

💡 WITH ROLLUP—集約値

SELECT
  Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;

の各集約関数クエリの後に、WITH ROLLUPを追加します.

24172⚠▼ORDER BYとは併用できません.HAVING-パケットデータのフィルタリング

SELECT
  Country, COUNT(*) AS Count
FROM Suppliers
GROUP BY Country
HAVING Count >= 3;

💡 WHEREはパケット前のデータ、HAVINGはパケット後の集約に用いられる.

SELECT
  COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')
GROUP BY OrderDate
HAVING Count > 2;

SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
  AveragePrice BETWEEN 20 AND 30
  AND MedianPrice < 40;

2.DISTINCT-重複値を削除します.

GROUP BYと異なり、集約関数は使用されません.

GROUP BYとは異なり、ソートされないため、より高速です.

SELECT DISTINCT CategoryID
FROM Products;
-- 위의 GROUP BY를 사용한 쿼리와 결과 비교

SELECT COUNT DISTINCT CategoryID
FROM Products;
-- 오류 발생

SELECT DISTINCT Country
FROM Customers
ORDER BY Country;

SELECT DISTINCT Country, City
FROM Customers
ORDER BY Country, City;

💡 GROUP BYとDISTINCTを併用

SELECT
  Country,
  COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;