MySQL I : Grouping

4. Grouping functions allow you to group records by category and perform some minimal statistics on the category. For example: to find the maximum and minimum values of a group:
SELECT MAX(invoice_amount) FROM invoices;
SELECT MIN(invoice_amount) FROM invoices;
5. To determine how much has been spent under each expense category:
SELECT SUM(expense_amount), expense_category FROM expenses
LEFT JOIN expense_categories USING (expense_category_id)
GROUP BY (expenses.expense_category_id);
6. To see how many invoices have been billed to each client, using the COUNT() aggregation function:
SELECT COUNT(*) AS num, client_name
FROM invoices LEFT JOIN clients
USING (client_id)
GROUP BY (clients.client_id)
ORDER BY num DESC;
7. You can alter the preceding query to reflect how many invoices are tied into each total amount:
SELECT COUNT(*), SUM(expense_amount), expense_category FROM expenses
LEFT JOIN expense_categories USING (expense_category_id)
GROUP BY (expenses.expense_category_id);
8. To use the INSERT_LAST_VALUE function, begin by inserting a new user into the users table.
INSERT INTO users VALUES (NULL, PASSWORD(‘D\’oh’), ENCODE(‘homerjsimpson’, ‘hello’));

Question: What is happening in the password?
9. Retrive that user’s “user_id”:
SELECT LAST_INSERT_ID();

Note: This can be used programmatically to capture a new record’s primary key for insertion as a foreign key in another table.
10. DISTINCT can be used to remove duplicate records from your view. For example, instead of listing every client for every invoice, this query will list only every unique client:
This shows duplicates:
SELECT invoices.client_id, client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;

This removes duplicates:
SELECT DISTINCT(invoices.client_id), client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;
11. To count how many different DISTICNT clients have been billed:
SELECT COUNT(DISTINCT(client_id)) FROM invoices;