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

How to have a ZF2 web service run a function on route match

php zend-framework2

I've got a REST web service set up using ZF2, and what I want to do is have my web service call an intervening function before actually running the action associated to the route that was matched.

need help with regex in javascript to replace string pattern

javascript jquery

Need help with regex javascript , i tried few but didn't work

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.