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!