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.

SELECT COUNT(*) AS n,
  ROUND(phot_g_mean_mag) AS bin,
  AVG(parallax) AS parallax_mean
FROM gaiadr1.tgas_source
GROUP BY bin
ORDER BY bin

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”).

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