COUNT and GROUP BYAggregation allows us to combine results by grouping records based on value. It is also useful for calculating combined values in groups. Let’s go to the surveys table and find out how many individuals there are. Using the wildcard * counts the number of records (rows):
We can also find out how much all of those individuals weigh:
We can output this value in kilograms (dividing the value by 1000.00), then rounding to 3 decimal places: (Notice the divisor has numbers after the decimal point, which forces the answer to have a decimal fraction)
There are many other aggregate functions
included in SQL, for example:
Now, let’s see how many individuals were counted in each
species. We do this using a
Ordering Aggregated ResultsWe can order the results of our aggregation by a specific column, including the aggregated column. Let’s count the number of individuals of each species captured, ordered by the count:
AliasesAs queries get more complex, the expressions we use can get long and unwieldy. To help make things clearer in the query and in its output, we can use aliases to assign new names to things in the query. We can use aliases in column names using
The
but using We can not only alias column names, but also table names in the same way:
And again, the
Aliasing table names can be helpful when working with queries that involve multiple tables; you will learn more about this later. The HAVING keywordIn the previous episode, we have seen the keyword For example, we can request to only return information about species with a count higher than 10:
The You can use the
Note that in both queries,
Saving Queries for Future UseIt is not uncommon to repeat the same operation more than once, for example for monitoring or reporting purposes. SQL comes with a very powerful mechanism to do this by creating views. Views are a form of query that is saved in the database, and can be used to look at, filter, and even update information. One way to think of views is as a table, that can read, aggregate, and filter information from several places before showing it to you. Creating a view from a query requires us to add
But we don’t want to have to type that every time we want to ask a question about that particular subset of data. Hence, we can benefit from a view:
Using a view we will be able to access these results with a much shorter notation:
What About NULL?From the last example, there should only be five records. If you look at the
But if we try to be extra clever, and find the average ourselves, we might get tripped up:
Here the
When we count the weight field specifically, SQL ignores the records with data missing in that field. So here is one example where NULLs can be tricky: Another case is when we use a “negative” query. Let’s count all the non-female animals:
Now let’s count all the non-male animals:
But if we compare those two numbers with the total:
We’ll see that they don’t add up to the total! That’s because SQL doesn’t automatically include NULL values in a negative conditional statement. So if we are querying “not x”, then SQL divides our data into three categories: ‘x’, ‘not NULL, not x’ and NULL; then, returns the ‘not NULL, not x’ group. Sometimes this may be what we want - but sometimes we may want the missing values included as well! In that case, we’d need to change our query to:
Which query can be used to perform calculations on a group of records?A totals query calculates subtotals across groups of records; a Total row calculates grand totals for one or more columns (fields) of data. For example, if you want to subtotal all sales by city or by quarter, you use a totals query to group your records by the desired category and you then sum the sales figures.
When creating an append query the table from which records are being extracted is called the?source table. in the make table or append query, the table from which records are being extracted.
Which query Wizard is used to locate records in one table that do not have related records?The Find Unmatched Query Wizard creates a query that finds records or rows in one table that have no related records in another table.
Which join uses records whether or not there are matching values in both tables?INNER JOIN
This type of join returns those records which have matching values in both tables. So, if you perform an INNER join operation between the Employee table and the Projects table, all the tuples which have matching values in both the tables will be given as output.
Which of the following is a collection of information and objects associated with a specific subject?A database is a collection of information that is related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection.
What contains all of the categories of data pertaining to one person Place Event thing or idea?Glossary. |