Counting results for cross join

Posted on

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 :

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;

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;

Leave a Reply

Your email address will not be published. Required fields are marked *