0

How I can get count results with cross join?

Example cross join:

SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2
  • 2
    Could you clarify what you mean by 'count results' as well as the particular RDBMS so that we can get you the proper syntax? – billinkc Dec 28 '12 at 22:52
  • I think count row after cross join. - I work with Oracle (11g). This is example to school. – user1871996 Dec 28 '12 at 23:03
2

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;
  • And how is that different from SELECT COUNT(*) FROM table1 t1 CROSS JOIN table1 t2 ? – ypercubeᵀᴹ Dec 28 '12 at 22:53
  • It's not... Most likely, the query optimizer will handle them the same. – Wes Dec 28 '12 at 22:56
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.