You can combine a column function with an INNER JOIN, using a GROUP BY clause. 

                      JOIN syntax:                                                 WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                     SELECT t1.column1 AS ‘title1′,
                       COUNT(t2.column2)                                    COUNT(t2.column2)
             FROM table1 [AS] ‘t1′                                   FROM table1 [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                    table2 [AS] ‘t2′,
                  ON t1.column1 oper t2.column2,         WHERE t1.column1 oper t2.column2,
           GROUP BY t1.column1                                 GROUP BY t1.column1
           ORDER BY t2.column2;                               ORDER BY t2.column2;

Where oper is a comparison operation and WHERE is used for additional filtering.

See Column Functions.