Clause Count (*) within a select


I have the following SQL query.

What I do not know how to do is that the count (*), returns one more column, with the total number of records.

The current query throws me errors, the count (*) is between

asked by Vitmar Aliaga 08.11.2016 в 00:04

3 answers


I do not find much sense to get the total number of records in a column, but since it is what you say you need, and assuming that the database engine is SQL Server 2012 or higher, you can use a window function to get it, for example:

select   count(1) over (partition by null) TotalRegistros
       , *
  from Personas
 where TipoPersonaId=46

pd. I assume it is SQL server by the form of the sentence in the question, mainly by the use of the function row_number() .

answered by 08.11.2016 / 00:33

Well, in case someone comes here looking for an answer, say that when using aggregate domain functions such as count or sum, they should only refer to a single attribute of the table, otherwise they do not work.

In the case of involving more than one attribute in the query, the group clause should be added, such as:

select count(*) as Productos, ReorderLevel
from dbo.Products
where dbo.Products.ProductName = 'Chai'
group by dbo.products.ReorderLevel;
answered by 20.04.2018 в 13:38

You can make 2 inquiries. One to know the number of records, and another to have the data.

select count(1) from Personas where TipoPersonaId=46

select * Personas where TipoPersonaId=46

Or you can make a single query with a subquery

select *, (select count(1) from Personas where TipoPersonaId=46) 
from Personas 
where TipoPersonaId=46
answered by 08.11.2016 в 00:28