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.
精彩评论