How I can get count results with cross join?
Example cross join:
SELECT t1.firstname, t2.lastname FROM table1 t1 CROSS JOIN table1 t2
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
;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;