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

### Question:

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.

Thoughts?

• 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.

### Solution:1

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  ``

### Solution:2

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
Previous
Next Post »