10. SELECT: Grouping

For histogram-like functionality, you can compute factor sets, i.e., subsets that have identical values for one or more columns, and you can compute aggregate functions for them.

  ROUND(phot_g_mean_mag) AS bin,
  AVG(parallax) AS parallax_mean
FROM gaiadr1.tgas_source

Note how the aggregate functions interact with grouping (they compute values for each group).

Also note the renaming using AS. You can do that for columns (so your expressions are more compact) as well as for tables (this becomes handy with joins).

For simple GROUP applications, you can shortcut using DISTINCT (which basically computes the “domain”).

ROUND(phot_g_mean_mag), ROUND(parallax)
FROM gaiadr1.tgas_source



Get the averages for the total proper motion from tgas_source in bins of one mag in mag g mean each. Let the output table contain the number of objects in each bin, too.

Markus Demleitner

Copyright Notice