Problem solve Get help with specific problems with your technologies, process and projects.

Instant grouping!

I have a table with two columns, C1 and C2. I want to create a report so that on grouping C1 it should retrieve as follows. Please help me with the query.

I have a table with two columns, C1 and C2 as follows:

C1      C2     
A       X      
A       Y      
B       Z 

I want to create a report so that on grouping C1 it should retrieve as follows:

C1      C2      Count(C1)
A       X       2
A       Y       2
B       Z       1

Please help me with the query. Thank you in advance!

That's quite an interesting situation. Normally, if we use GROUP BY on C1, we get one row returned per C1. But the desired results to be retrieved are all the original rows. So we can't use GROUP BY.

Fortunately, there is another way to achieve grouping—the correlated subquery.

select C1
     , C2
     , ( select count(*) 
           from thetable
          where C1 = T.C1 ) as thecount
  from thetable as T

Each row in the original table will be returned, along with a count of all the rows for that value of C1. Instant grouping!

Yes, the same count appears on the row with A and X, and the row with A and Y. Notice that the subquery uses only the value A. Does this imply that since there are two A rows, the subquery will be executed twice?

No, it does not. <smile>.

This was last published in August 2006

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.