Take the 2-minute tour ×
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's 100% free, no registration required.

How I can get count results with cross join?

Example cross join:

SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2
share|improve this question
1  
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
add comment

2 Answers

up vote 2 down vote accepted

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;
share|improve this answer
    
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 Crockett Dec 28 '12 at 22:56
    
Thanks @yperhube, added your version as well. –  dabest1 Dec 28 '12 at 22:58
add comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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