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.

Answer 1

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.

Answer 2

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