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:

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:

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.