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>.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.