'Count all lines in the select statement, result value have column all the line number in select [closed]

![enter image description here][1]

I want in select add a column "all line number" is all line was get(not use subquery).

Can use any data.

This my instance so everyone understands what are I need it.

select *, "all line number"
from SysUserInSubRoleRes
group by SubRoleId
having count(SubRoleId) > 2
order by SubRoleId desc
OFFSET 0 ROWS
FETCH NEXT 2  ROWS ONLY'

Value default:
SubRoleId
--------
  v1
  v2
  v3
  v1
  v2
  v2
  v1
  
Result Value 
add column alllinenumber

SubRoleID  |  AllLineNumber
  v1       |      3
  v2       |      3
sqlsql-server


Solution 1:[1]

Your expected output cannot be the result of your query since subroleid 1 has a alllinenumber = 1 and 2 as 2 neither of which pass the count test of > 2

You may be looking for something like

drop table t
go
create table t(subroleid int)
go
insert into t values (1),(2),(2),(2)

select subroleid, count(*) as "all line number"
from t
group by SubRoleId
having count(SubRoleId) > 2
order by SubRoleId desc

subroleid   all line number
----------- ---------------
2           3

but without sample data and expected output it's hard to be sure.

Edit 1.

drop table t
go
create table t(subroleid varchar(2))
go
insert into t values ('v1'),('v2'),('v3')


select t.subroleid,  cnt "all line number"
from t
cross join (select count(*) cnt from t) s
where cnt > 2
order by SubRoleId 
OFFSET 0 ROWS
FETCH NEXT 2  ROWS ONLY


subroleid all line number
--------- ---------------
v1        3
v2        3

Solution 2:[2]

If you want row number, then you can easily generate it

select SubRoleId, ROW_NUMBER() OVER(ORDER BY SubRoleId DESC) AS [all line number]
from SysUserInSubRoleRes
group by SubRoleId
having count(SubRoleId) > 2
order by [all line number]

Solution 3:[3]

SELECT COUNT(SUBROLEID) AS [ALL LINE NUMBER]
FROM SYSUSERINSUBROLERES
GROUP BY SUBROLEID
HAVING COUNT(SUBROLEID) > 2
ORDER BY SUBROLEID DESC

Hope this Query Works for your case

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Venkataraman R
Solution 3

Related Questions