Tutorial :How can I do a self-join with count/'group by' comparisons?


Given the following table (how to format those correctly here?)

primary secondary  A            a  A            b  A            b  B            a  B            a  B            b  

I'm trying to get comparitive group-by counts using a self join.

Getting the following result set is easy:

Primary Secondary     Count  A            a          1  A            b          2  B            a          2  B            b          1  

with something like:

select primary,secondary,count(*) from foobar group by primary,secondary

But what I REALLY want is this:

Primary  Secondary Count  Primary  Secondary    Count  A        a         1      B        a             2  A        b         2      B        b             1  

When counts and group bys aren't involved, self-joins are simple. But I can't seem to navigate my way around doing this.

Does the "self join AFTER group by" make this impossible to do? If I have to play temp table games I'll do it (though I'd rather not) since the real goal is a single block of sql (something I can script), more than a single select statement.

At the moment I'm doing the former and manually padiddling the data.


  • M

Hmm... Of course all the stuff in my head is obvious to ME ;)

The "business logic" I'm trying to achieve is "compare the count of 'secondary' in 'primary A' to the count of 'secondary' in 'primary B' which is why I didn't write out the B:B result set lines. But I figure any clause that gets them in there can be filtered anyway.


This should get you close. I'm not sure how you determine that only the "A" primary rows get shown as the first couple of columns, so I can't account for that. Why isn't there a:

B b 1 B b 1  

for example?

SELECT      SQ1.primary,      SQ1.secondary,      SQ1.[count],      SQ2.primary,      SQ2.secondary,      SQ2.[count]  FROM  (      SELECT          primary,          secondary,          COUNT(*) AS [count]      FROM          Foobar      GROUP BY          primary,          secondary  ) AS SQ1  LEFT OUTER JOIN  (      SELECT          primary,          secondary,          COUNT(*) AS [count]      FROM          Foobar      GROUP BY          primary,          secondary  ) AS SQ2 ON SQ2.primary = SQ1.secondary  


If you are using SQL Server you can do this easily using CTE

If not, you can do this kind of a select (OTTOMH)

SELECT T1.Col1, T1.Col2, T2.Col3, T2.Col4, MyCount  FROM Table1 T1,   (      SELECT Col3, Col4, COUNT (*) as MyCount      FROM Table2      Group by Col3, Col4  ) as T2  WHERE T1.Col1 = T2.Col3  GROUP BY T1.Col1, T1.Col2, T2.Col3, T2.Col4  

As your query gets more complicated, take a look at your execution plan for optimum performance.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »