Counting results for cross join

Question

How I can get count results with cross join?

Example cross join:

SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2

Answer 1

You could do this:

SELECT COUNT(*) FROM table1 t1 CROSS JOIN table1 t2;

or this format which will allow you to copy and paste any query without rewriting it and use it as a subquery:

SELECT COUNT(*) FROM (
  SELECT t1.firstname, t2.lastname
  FROM table1 t1 CROSS JOIN table1 t2
) t3;

Answer 2

The same way you get a count with any other aggregate---all the columns that participate in the query need to be accounted for either in an aggregate or a GROUP BY.

;WITH table1(firstname, lastname, extra) AS
(
    SELECT 'bob', 'york', 'tuff'
    UNION ALL SELECT 'annie', 'murloc','guts'
)
SELECT
    t1.firstname
,   t2.lastname
,   COUNT(1) AS rc
FROM
    table1 t1
    CROSS JOIN table1 t2
GROUP BY
    t1.firstname
,   t2.lastname;