SELECT 
    AVG(buyPrice) average_buy_price
FROM 
    products;

SELECT 
    productLine, 
    AVG(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY productLine;

SELECT 
    COUNT(*) AS total
FROM 
    products

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine
ORDER BY productLine;

SELECT 
    productCode, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderDetails
GROUP BY productCode
ORDER BY total DESC;

SELECT 
    productCode,
    productName,
    SUM(priceEach * quantityOrdered) total
FROM
    orderDetails
        INNER JOIN
    products USING (productCode)
GROUP BY productCode
ORDER BY total;

SELECT 
     MAX(buyPrice) highest_price
FROM 
     products;

SELECT 
    productLine, MAX(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY MAX(buyPrice) DESC;

SELECT 
    MIN(buyPrice) lowest_price
FROM 
    products;

SELECT 
    productLine, 
    MIN(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY MIN(buyPrice);

SELECT 
    firstName,
    lastName,
    GROUP_CONCAT(
    DISTINCT customername
        ORDER BY customerName) customers
FROM
    employees
INNER JOIN customers 
    ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;