SQL grouping

I have a table with the following columns:


A   B   C
---------
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 the B and C columns and count the distinct values of the A column. But if there are two ore more rows where the value on the A column is the same I want to get the maximum value from the B column.

If I do a simple group by the result would be:


B   C    Count
--------------
10  X    1
11  X    1
15  X    1
20  Y    2
15  Y    1

What I want is this result:


B   C    Count
--------------
11  X    1
15  X    1
20  Y    2

Is there any query that can return this result. Server is SQL Server 2005.

Answers


I like to work in steps: first get rid of duplicate A records, then group. Not the most efficient, but it works on 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

I have 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 it gives me:

 B     C     Count    
 ----  ----  -------- 
 15    X     1        
 15    y     1        
 20    y     2        

Need Your Help

Spray microservice assembly deduplicate

scala sbt akka spray microservices

I'm using this template to develop a microservice:

Getting started with Standard ML

functional-programming sml ml

I'm looking for some kind of "ML for beginners" guide - google has led me to some obscure mailing lists or way-over-my-head texts so far.