-1

My excel sheet having a column Count is responsible for counting how many times one registration number is repeated as you can see in the given picture. Whenever I am going to add any new record in my excel table this column go up and count how many records are there as like my reg_no

Let us take Example:

If we add new record at 17th id with

  • Reg_no = 3591
  • Name = 'dani'
  • grade = 'A'
  • Count ?

Now it will be like Count = 4

I want to convert this table into a SQL query and I am having a problem converting this Count column that how I am going to calculate this count column in SQL

Does anyone know? please help

MY Excel Sheet Screenshot

Daniyal Tariq
  • 40
  • 7
  • This is just condition aggregation. `COUNT(CASE WHEN {expression} THEN 1 END)` – Thom A Jul 22 '20 at 14:06
  • As you can see above in the heading in excel they have specified the column to stop at B5 and respectively this formula change in excel when I click on 6th row then it will show B6. – Daniyal Tariq Jul 22 '20 at 14:12
  • *"how my query is going to above and count only on that position where I am currently not on the whole table"* That sentence doesn't make any sense, I'm afraid. – Thom A Jul 22 '20 at 14:14
  • Does this answer your question? [Sql Server equivalent of a COUNTIF aggregate function](https://stackoverflow.com/questions/582637/sql-server-equivalent-of-a-countif-aggregate-function) – NvrKill Jul 22 '20 at 14:26
  • I am stuck with How to add Range in the query as it was in the COUNTIF function of Excel for reference you can look over to the picture I have provided in my Question. – Daniyal Tariq Jul 22 '20 at 14:32
  • @RyffLe no it didn't answer my question if you read again the description and the image I have provided now then you will get to know my problem clearly as I have edited my post with some better description this time. – Daniyal Tariq Jul 24 '20 at 10:36

2 Answers2

0

step 1 create a temp table with empty column

SELECT  * , null as desired_column , 
into #yourTable_t1
FROM #yourTable j;

step 2 create a cursor to calculate your desired_column and update temp_table

begin
declare @row int, @order int, @prod varchar(100), @prod_count int =0 ;
declare prod_cur cursor for 
    SELECT   row_num,  MyColumn1,MyColumn2 
    FROM #yourTable_t1 ;
open prod_cur;
fetch next from prod_cur into @row , @order, @prod;
while (@@FETCH_STATUS=0)
begin 
    
set @prod_count= ( select count(MyColumn2) from #yourTable_t1 where 
MyColumn2= @prod and ROW_NUM <= @row);

update #yourTable_t1
set desired_column = @prod_count
where ROW_NUM= @row;

fetch next from prod_cur into @row , @order, @prod;
end;
close prod_cur;
deallocate prod_cur;
--select * from #yourTable_t1 order by MyColumn2;
end;

Good Luck!

Daniyal Tariq
  • 40
  • 7
0

This can be done using window functions

count(*) over (partition by rege_no order by id) as count

Online example