Aggregate Functions
All the queries you have written so far return every
record that matches the criteria in the where block. But with SQL, you can
also return statistical summaries of all matching records. For
example, look at the query below:
select
min(dec) as min_dec, max(dec) as max_dec, avg(dec) as avg_dec
from
photoObj
where
run = 5112
|
The query retrieves the minimum, maximum, and average declination
of one of the SDSS's equatorial stripes (the area of the survey near the
celestial equator, dec = 0). The commands min(x), max(x), and avg(x)
are aggregate functions, named because they operate on an
aggregate, or sum, of all the matching records. SQL's aggregate functions
are listed in the table below:
Aggregate Function |
Returns... |
min(x) |
the smallest value in column x |
max(x) |
the largest value in column x |
avg(x) |
the average value in column x |
stdev(x) |
the standard deviation of the values in column x |
count(x) |
the number of values in column x |
count(*) |
the number of records in the table being searched |
The difference between count(x) and count(*) can appear confusing, but they
are usually used in different situations. Use count(*) to find out the number
of records in a table - how big the table is. The command "select count(*)
from specObj" returns 608801 - meaning there are 608,801 separate
records in the specObj table. The command "select count(*) from PhotoPrimary"
returns 180,235,043.
Unlike count(*), count(x) is usually used with a characteristic listed
in the where block. For example, the query below returns the number of
records in the specObj table that have redshift between 0.5 and 1: 14,759.
So the number of objects with redshift between 0.5 and 1 found by count(x)
is much less than the total number of objects in specObj, found by count(*).
select
count(z) as num_redshift
from
specObj
where
z BETWEEN 0.5 AND 1
|
Try It!
Try pressing the buttons "Query 1" and "Query 2" below. These
buttons will make the two queries above (in the purple boxes) appear in the
query window. Press Submit to execute the queries. Do you get what you
expected? Do the results seem reasonable to you?
Click Next when you are ready to move on.
|