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

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. You'll find out in a moment - but first, look at the specClass column of specObj table in the Schema Browser to remind yourself what types of objects the different specClass numbers symbolize.

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

select
specClass, count(z) as num_redshift

from
specObj

where
z BETWEEN 0.5 AND 1

group by specClass

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.

You can also use the SpecClass table to get the English name of the spectral class. You would have to use a join between SpecObj and SpecClass. The query below does the same search as the previous query, but returns names instead of numbers:

select
c.name as class, count(s.z) as num_redshift

from
specObj s, specClass c

where
s.specclass = c.value and
z BETWEEN 0.5 AND 1

group by c.name

Similarly, the PhotoType table will return the English names for the photometric types star, galaxy, etc.

Try It!

Press Query 1 to load Query 1 into the Query window, then press Submit to execute the query. Verify that the total count of objects returned is 4,577, just as it was when you counted all objects between z = 0.5 and 1. Was the distribution of objects - the numbers of each type of object - what you expected? Try changing the redshift limits and see how the distribution of objects changes. Press Query 2 to load Query 2, and verify that it returns the same distribution of objects as Query 1.


Format HTML XML CSV



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