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.

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.

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.