SQL grouping
I have a table with the following columns:
ABC --------- 1 10 X 1 11 X 2 15 X 3 20 Y 4 15 Y 4 20 Y
I want to group the data based on columns B and C and count the different values of column A. But if there are two more rows where the value of column A is the same, I want to get the maximum value from column B.
If I do a simple group by result it would be:
BC Count -------------- 10 X 1 11 X 1 15 X 1 20 Y 2 15 Y 1
I want to get this result:
BC Count -------------- 11 X 1 15 X 1 20 Y 2
Is there any query that can return this result. SQL Server 2005.
0
Albert
source
to share
4 answers
I like working in steps: first get rid of the duplicate A records and then the group. Not the most efficient, but it works for your example.
with t1 as (
select A, max(B) as B, C
from YourTable
group by A, C
)
select count(A) as CountA, B, C
from t1
group by B, C
+2
Tiberiu ana
source
to share
I actually tested this:
SELECT
MAX( B ) AS B,
C,
Count
FROM
(
SELECT
B, C, COUNT(DISTINCT A) AS Count
FROM
t
GROUP BY
B, C
) X
GROUP BY C, Count
and this gives me:
B C Count
---- ---- --------
15 X 1
15 y 1
20 y 2
+2
Kris
source
to share
Check it. This should work in Oracle, although I haven't tested it,
select count(a), BB, CC from
(
select a, max(B) BB, Max(C) CC
from yourtable
group by a
)
group by BB,CC
0
Dheer
source
to share
WITH cteA AS
(
SELECT
A, C,
MAX(B) OVER(PARTITION BY A, C) [Max]
FROM T1
)
SELECT
[Max] AS B, C,
COUNT(DISTINCT A) AS [Count]
FROM cteA
GROUP BY C, [Max];
0
Thuglife
source
to share