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!
Requires Free Membership to View
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 first published in August 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation