Count of two columns while keeping the remaining columns-CodePudding

I need to figure out how to count the combinations of two columns while keeping the rest of the columns visible. This is what I have

select
Table1.A as Column1,
Table1.B as Column2,
Table1.C as Column3,
CASE WHEN 
Table1.D like '003%' THEN Table1.E ELSE Table2.A END as as Column4

FROM Table1
INNER JOIN Table2 on Table1.A = Table2.A AND Table2.X like '0002%'

The code above produces Columns 1 - Column 4 however I need a count of the combination of column 2 and column 4(which is the result of a case statement) as the example below. I don't know how groupby will work because I will need to display all the columns. How do I produce the column of 'Count_of_Col24'?

Column1	Column2	Column 3	Column4	Count_of_Col24
1	123	AB	BA	1
2	123	AC	BB	2
3	123	AD	BB	2

CodePudding user response：

You can try to use COUNT window function, PARTITION BY your logic which you want count of the combination.

SELECT t1.*,
       COUNT(*) OVER(PARTITION BY Column2,Column4) Count_of_Col24
FROM (
    select
        Table1.A as Column1,
        Table1.B as Column2,
        Table1.C as Column3,
        CASE WHEN  Table1.D like '003%' THEN Table1.E ELSE Table2.A END as as Column4
    FROM Table1
    INNER JOIN Table2 on Table1.A = Table2.A AND Table2.X like '0002%'
) t1