Create auto counter 1,2,3,4,5...etc

Question

I need to insert new first column counter

1

2

3

4

5

6..etc in this code

String saveStaff = " Select roba as 'Naziv artikla', SUM (cast(kolicina as float)) as 'Kolicina' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables group by roba";

Some recomandition?

Shahriar Nikkhah · Answer

First try SELECT roba as 'Naziv artikla' , SUM ( cast (kolicina as float )) as 'Kolicina' from ( select roba , kolicina from mp_racun_roba union all select roba , kolicina from mp_faktura_roba ) as alltables group by roba and see if its working , then add the ROW_NUMBER and test your field "kolicina" from the first line needs to be in the GROUP BY check this link for better understanding of the combination of the ROW_Number and group by https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017

Goran Bibic · Answer

Like this? SELECT ROW_NUMBER() OVER (PARTITION BY kolicina ORDER BY roba DESC) as 'redni_broj' , roba as 'Naziv artikla' , SUM (cast(kolicina as float )) as 'Kolicina' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables group by roba Error is Msg 8120, Level 16, State 1, Line 1 Column 'alltables.kolicina' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sai Sherlekar · Answer

Please read below link which will help you to resolve https://www.c-sharpcorner.com/UploadFile/b1df45/row-number-function-in-sql-server/