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

Goran Bibic

8y827SQL

I need to insert new first column counter

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted 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 Bibic8y ago
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 Sherlekar8y ago
Please read below link which will help you to resolve

https://www.c-sharpcorner.com/UploadFile/b1df45/row-number-function-in-sql-server/