How can I combine count / 'group by' comparisons on my own?

Question

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

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

I am trying to get comparative group calculations using self-join.

Getting the following set of results is easy:

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

with something like:

select primary, secondary, counter (*) from the foobar group primary, secondary

But I really want this:

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

When numbers and group bytes are not involved, self-joins are simple. But it seems like I can't do it.

Does this make “independent AFTER joining” impossible? If I need to play temp table games, I will do it (although I wouldn’t), since the real goal is one sql block (something I can script), more than one select statement.

.

?

• M

... , ;)

" ", , - " " "" " " "" ", B: B, , , , .

Answer 1

. , , "A" , . :

B b 1 B b 1

?

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

Answer 2

SQL Server, , CTE

, 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

, , .