SQL: Grouping, joins and Injections
Explore SQL techniques for aggregating data with GROUP BY and HAVING clauses, joining multiple tables using INNER and OUTER joins, and securing your PHP applications by preventing SQL injection with prepared statements. Understand how to work with relational databases effectively while maintaining application security.
We'll cover the following...
Let’s finish our introduction to SQL by learning ways to aggregate data from multiple records and even multiple tables in our queries.
Grouping
We can aggregate multiple records to get statistical analysis. We do this by using the group functions. The most common of them is count().
Here’s how we can get the count of all the plays from our database:
Once the database sees the group function count(), it groups all the records (which is its default behavior) and returns the count of the records in this group. We can specify how we want to group records and use multiple grouping functions. To do this, we provide the GROUP BY clause and the comma-separated list of fields.
Let’s see how many plays we have by each author. Let’s also find the shortest and longest play duration of each ...