Let’s exhaust your abilities before shopping for more resources so we know what to look for. Sound good?
First, what does our table look like?
There are 200 rows of data. The column we are querying is
price, albeit we don’t want the whole column, just the average price. The table from our query will have a single value.
This is the role of an aggregate function. Apply the function to a column of data and return a value. Well, one role. In this example we’re after one number. The average price given a column of 200 values.
The role of a SELECT statement is pretty clear. Tell SQL to sit up. Parameters and clauses are following… All we need is a table to act upon.
Remember the statement,
SELECT * FROM fake_apps;
That will return the complete table. The wildcard will bind to all the columns.
If we only want to see one column,
SELECT price FROM fake_apps;
Once the column is confirmed we can narrow this down to the singular value we seek, the average.
SELECT AVG (price) FROM fake_apps;
We have still selected the entire column, except this time we have handed it to the AVG() function to return a table with only one row, one column, one value. Essentially, we have short-circuited the normal building process and will now produce a 1 X 1 table, and populate it with the computed value returned by the function.
The final step is only acting on the return value of the AVG() function. The column table is already out of the picture.
SELECT ROUND (AVG (price), 2) FROM fake_apps;
The second positional argument,
2 denotes the precision. Our reported value will be in dollars and cents (tendered currency format).
If there is anything we covered above that is not clear, then search is your friend. That’s how we would find the resources you are looking for.
Above will produce a table with no column heading. SQL used our function expression as the heading by default. To round out our report we can supply the missing heading…
SELECT ROUND (AVG (price), 2) AS 'Average Price' FROM fake_apps;
For more fun we can invoke another aggregate function on our column query…
SELECT ROUND (AVG (price), 2) AS 'Average Price', COUNT (*) AS 'Rows' FROM fake_apps;
Note how this time the wildcard is scoped to the rows, not the columns.
Adding to the report parameters…
ROUND (AVG (price), 2) AS 'Average Price',
MIN (price) AS 'Lowest Price',
MAX (price) AS 'Highest Price',
COUNT (*) AS 'Rows'