Brtdku

I have a table with below column & row values and want a resultset as given below. I tried many queries but could not getthe resultset which I want.
Is there any simplest way to achieve this.

Column1 Column2  Column3 Column4 Column5

AAA     000000      BG1   12345   North

AAA     111111      BG2   23456   South

BBB     000000      BG3   12346   EAST

AAA     000000      BG2   12345   West



Select Column1,Count( Distinct Column1,Column2),Count(Distinct Column1,Column3),Count(Column1,Column4,Column5) From #Temp

Expected Resultset:

Name C2Count C3Count C4Count

AAA    2      2        3     

BBB    1      1        1

I don't see anyway to avoid doing two separate aggregations. One can handle the single distinct counts while the other can handle the two column count:

WITH cte1 AS (

    SELECT

        Column1 AS Name,

        COUNT(DISTINCT Column2) AS C2Count,

        COUNT(DISTINCT Column3) AS C3Count

    FROM yourTable

    GROUP BY Column1

),

cte2 AS (

    SELECT Name, COUNT(*) AS C45Count

    FROM

    (

        SELECT DISTINCT Column1 AS Name, Column4, Column5 FROM yourTable

    ) t

    GROUP BY Name

)



SELECT

    t1.Name,

    t1.C2Count,

    t1.C3Count,

    t2.C45Count

FROM cte1 t1

INNER JOIN cte2 t2

    ON t1.Name = t2.Name;

Demo

Perhaps you're looking for this ? :

DECLARE 

    @tb TABLE

    (

        Column1 VARCHAR(50)

    ,   Column2 VARCHAR(50)

    ,   Column3 VARCHAR(50)

    ,   Column4 INT

    ,   Column5 VARCHAR(50)

    )

INSERT INTO @tb VALUES 

('AAA','000000','BG1',12345,'North'),

('AAA','111111','BG2',23456,'South'),

('BBB','000000','BG3',12346,'EAST'),

('AAA','000000','BG2',12345,'West')



SELECT

    Column1 [Name]

,   COUNT(DISTINCT Column2) C2Count 

,   COUNT(DISTINCT Column3) C3Count 

,   SUM(C45Count) C4Count

FROM (

    SELECT *, COUNT(Column1) OVER(PARTITION BY Column1, Column4, Column5) C45Count

    FROM @tb 

) D

GROUP BY 

    Column1

I am a little confused on how you are wanting the C4count calculated. Using this data(thanks @iSR5):

CREATE TABLE #Table

    (

        Column1 VARCHAR(50)

    ,   Column2 VARCHAR(50)

    ,   Column3 VARCHAR(50)

    ,   Column4 INT

    ,   Column5 VARCHAR(50)

    )

INSERT INTO #Table VALUES 

('AAA','000000','BG1',12345,'North'),

('AAA','111111','BG2',23456,'South'),

('BBB','000000','BG3',12346,'EAST'),

('AAA','000000','BG2',12345,'West')

You can use a pretty basic Statement to achieve the desired results:

SELECT   Column1 AS [Name]

        ,COUNT(DISTINCT t.Column2) AS C2Count

        ,COUNT(DISTINCT t.Column3) AS C3Count

        ,COUNT(t.Column4) AS C4Count

FROM #Table t

GROUP BY t.Column1

Results:

Name C2Count C3Count C4Count

AAA    2      2        3     

BBB    1      1        1

This will count how many distinct matches are in each column for AAA and BBB for column2 and column 3, and count overall matches for column4. Your desired results don't show anything for column5.

If there is some other sort of calculation you are wanting, could you please clearly describe what you are looking for so I can adjust my code to show what it is you are looking for.