Clever Geek Handbook

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


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


source


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


source


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


source 


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


source






More articles:


All Articles