Search code examples
sqlsql-serverwindow-functionssql-server-2019

Count column without grouping

Below is my 1st Table1

Column A Column B
A Cell 2
A Cell 4
B Cell 2
A Cell 4
B Cell 2
A Cell 4

Below is my result that I want to achieve based on Table1 I need column C in select query which gives the count of number of time it appears in column A in each row of column C. I am using SQL Server.

Column A Column B Column C
A Cell 2 4
A Cell 4 4
B Cell 2 2
A Cell 4 4
B Cell 2 2
A Cell 4 4

I have tried below query but due to groupby clause it is showing only two rows which is not my desired result

Select ColumnA, Count(ColumnA) as ColumnC
from Table1
group By columnA
Column A Column B
A 4
B 2
Solution

  • I got the result using Partition

    I have tried below solution

    SELECT ColumnA
    , ColumnB
    , Count(ColumnB) OVER (PARTITION BY ColumnA) as Intensity
FROM [dbo].[Table1]
    Column A Column B Intensity
    A Cell 2 4
    A Cell 4 4
    B Cell 2 2
    A Cell 4 4
    B Cell 2 2
    A Cell 4 4