Sloan Digital Sky Survey
SkyServer DR15  
Not logged in Login Help
 

SQL Tutorial
 Back to Help
 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
SQL Tutorial

The Group By Command

The previous query searched through the entire SpecObj table and returned the number of objects between redshifts 0.5 and 1. But instead of lumping all objects together, you can display results sorted by another trait. You can group all results containing a given trait with SQL's group by command. For example, you could group the results of the query above to show how many of the objects between z = 0.5 and 1 are stars, galaxies, quasars, or other.

To collect together all the different objects in the query above, you would use the command group by class. Add this command to the end of the query, after the where block. Be sure to select the class column in the select block, so you can know which class column corresponds to which result. So the new query will look like this:

select
    class, count(z) as num_redshift
from
    specObj
where
    z BETWEEN 0.5 AND 1
group by 
    class

The group by command will work with any of the six aggregate functions. The column in the group by command must be in one of the tables in the from block. The column used in the group by command must be returned in the select block, either by itself or as part of an aggregate function.

Try It!

Format HTML XML CSV

Enter your SQL query in the text box. The query is limited to 10 minutes and 100,000 rows.