# Count of two columns while keeping the remaining columns

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

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
```
3 People found this is helpful