How to Get Count() and Rows from One SQL Query in SQL Server

Getting Count and Rows in same query

You can always try something like this:

SELECT
    COUNT(*) OVER (),
    (list of your other columns here)
FROM dbo.YourTableNameHere

The OVER() clause will give you a count of all rows right in your query.

SQL count rows in a table

Yes, SELECT COUNT(*) FROM TableName

How to Get Count(*) and table data based on where condition in single query

If you want to return the count in the same recordset without returning multiple recordsets you can use count() with over()

For example

select *, count(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

or if the results are larger than an int as suggested by your output data type,

select *, count_big(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

Also as mentioned, if you want to keep a second result set you can assign the count to your output variable using @@rowcount or rowcount_big().

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id ,
 date
HAVING
COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no , 
    date,
        COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no , 
            zip, 
            date
         FROM
            payment 
    
        ) 
        payment
 GROUP BY
    
    user_id,
    account_no , 
    
    date
HAVING COUNT(*) > 1

How to get multiple counts with one SQL query?

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

SQL query to get count of rows with more than one row in another table

Just use your original query as a derived table (put it in a subselect):

SELECT COUNT(*)
FROM (
    SELECT count(*) AS C
    FROM Resources as r 
        INNER JOIN ResourceFields AS rf 
            ON rf.ResID = r.ResID 
                AND rf.name = 'ContactName' 
    WHERE r.type = 1 
    GROUP BY rf.ResID 
    HAVING COUNT(rf.Value) > 1;
) s

best way to get count and distinct count of rows in single query

Why don't you just put the subquery inside another query?

select count(*),
       (select count(*) from (select distinct * from table))
from table;

Counting rows based on a criteria in sql server

Is this what you want?

select count(*) as num_records,
       sum(case when onlineoffline = 'online' then 1 else 0 end) as num_online_record
from (select t.*,
             min(case when onlineoffline = 'online' then trxndate end) over () as min_online_td
      from t
     ) t
where trxndate > min_online_td;

The subquery calculates the date for the first online record. The rest of the query just does the counts that you want.

Get row count including column values in sql server

@Jim H is almost right, but chooses the wrong ranking function:

create table #T (ID int)
insert into #T (ID)
select 1 union all
select 2 union all
select 3
select ID,COUNT(*) OVER (PARTITION BY 1) as RowCnt from #T
drop table #T

Results:

ID  RowCnt
1   3
2   3
3   3

Partitioning by a constant makes it count over the whole resultset.

---

Related Topics