Tutorials
Help
 Archive Intro
 Table Descriptions
 Schema Browser
 Glossary
 Algorithms
 Introduction to SQL
 Form Query User Guide
 Query Limits
 How To
     - Search
     - Graph
 FAQ
 API
 sdssQA
 Download
 SkyServer Sites
 SkyServer Traffic Page
 Web Browsers
 Contact Help Desk
Searching
 Back
 1. Introduction
 2. A Simple Query
 Practice
 3. Common Searches
 4. More Samples
 Practice
 5. Multiple Tables
 Practice
 6. Aggregate Fcns.
 7. Group By
 8. Order By
 Practice
 9. Views
 10. Functions
 Practice
 11. Conclusion

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 = 1458

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 163,901 - meaning there are 163,901 separate records in the specObj table. The command "select count(*) from PhotoPrimary" returns 52,530,681.

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: 4,577. 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.


Format HTML XML CSV



Enter your SQL query in the text box. The query is limited to 90 seconds and 1,000 rows.