Compare a SQL count result with previous result value - how many new records since my last select count

sql select distinct values and count of each
count number of rows in sql query result
sql count distinct values
sql count rows with same value
sql count group by
sql count where
sql count in where clause
how to use count in join query in sql server

I have a script inserting new values on a table. When I select count(*) the results are shown. Is there a way to compare this value with the new one when I repeat the query?

Considering that the script is still inserting new values, I want to know how many new records since my last select count.

You can't easily do this because each time you run your query (if running manually) it will reset any variables you have. There are 3 options I can think of:

  1. Similar to Gordon's code, but you have to manually save the value each time you run it
declare @saved_count int = 0; -- THIS MUST BE MANUALLY UPDATED EACH TIME YOU RUN IT

-- Get the different since the last run
select count(*)-@saved_count
from MyTable;

-- Get the current value => manually copy to @saved_count above
select count(*)
from MyTable;
  1. Store the value in more permanent storage as per Lukasz's answer (you can use a global temp table which will go once the connection is dropped which won't affect production).

  2. You can run an automatic query as follows which keeps track of the current value and regularly prints out some details for you.

declare @CurrentCount int = 0, @LastCount int = 0;

while 1 = 1 begin
  select @CurrentCount = count(*)
  from MyTable;

  select getdate(), @CurrentCount - @LastCount;
  raiserror('Force a buffer flush',0,1) with nowait;

  set @LastCount = @CurrentCount;

  waitfor delay '00:00:10'; -- Wait 10s
end

SQL Server COUNT Function By Practical Examples, This tutorial shows you how to use the SQL Server COUNT() function to get DISTINCT instructs the COUNT() function to return the number of unique non-null values. a new table named t , insert some data into the table, and query data against it: The COUNT(*) returns all rows in a specified table as illustrated in the� Interpretation of Results SELECT COUNT(*) Looking at the execution plan, the Aggregate operation AggType is countstar and the ScalarString is Count(*). SQL Server is actually pretty smart as it can choose an efficient route to get the record count. In this case, there is a clustered index and a non-clustered index which does not allow NULL.

You could materialize it:

CREATE TABLE tab_stat(id INT IDENTITY(1,1), t DATETIME DEFAULT GETDATE(), cnt BIGINT);

INSERT INTO tab_stat(cnt)
SELECT COUNT(*)
FROM tab_name;

SELECT *, cnt - LAG(cnt,1,0) OVER(ORDER BY id) AS diff
FROM tab_stat
ORDER BY id DESC;

db<>fiddle demo

Overview of the SQL Count Distinct Function, We want to know the count of products sold during the last quarter. We can use SQL Count Function to return the number of rows in the 2 rows. SQL COUNT Distinct does not eliminate duplicate and NULL values from the result set. If we use a combination of columns to get distinct values and any of the� The stored proc below will compare the output resultset of 2 stored procedures, or 2 statements. The key here is the SP does not need to know the structure or schema of the result set, thus you can arbitrarily test any SP.

In general, you cannot do this. However, if your table has an identity column you could remember it and then do:

select count(*)
from t
where id > @saved_id;

You could do something similar with a creation date/time if that is available.

You would, of course, have to assign the maximum id (or create time) to a variable for this to work the next time.

SQL COUNT: The Ultimate Guide To SQL COUNT Function, You can use the COUNT function in the SELECT statement to get the number of The result of the COUNT function depends on the argument that you pass to it. the number of rows in a table including the rows that contain the NULL values. To find how many employees who work in the department id 6, you add the� SELECT COUNT(IFNULL(code, 1)) FROM code_values; Counting Non-null Values. The COUNT(expr) version of the COUNT function also accepts individual column names, the effect of which is that COUNT(column_name) will return the number of records where column_name is not NULL. Hence, the following SELECT query would fetch the number of rows where the

SQL: COUNT Function, The SQL COUNT function is used to count the number of rows returned in a SELECT statement. SELECT COUNT(aggregate_expression) FROM tables [ WHERE conditions] [ORDER BY expression The expression used to sort the records in the result set. Example - COUNT Function only includes NOT NULL Values. SQL Code: SELECT COUNT( *) as "Number of Rows" FROM orders; Output: Number of Rows ----- 36 SQL COUNT( ) with where clause. The WHERE clause can be used along with SQL COUNT() function to select specific records from a table against a given condition. Example: Sample table: orders

MySQL COUNT - Counting Rows in a Table, The COUNT(*) function returns the number of rows in a result set returned by a of distinct rows that do not contain NULL values as the result of the expression. SELECT COUNT(*) FROM count_demos WHERE val = 2; Previous MySQL AVG All MySQL tutorials are practical and easy-to-follow, with SQL script and� In above result set the 1 and 4 ID of IV is removed i want it this way. And then i want to calculate the total count of CurrentStepName. I hope i have made myself clear. Looking forward to your help as always. What I have tried: Tried many things but still unable to get the result

Comparing Metrics Over Time in SQL, We will use values from this column to compare metrics over time. When comparing the current year to previous years, you may include If you want to “ sanity check”, try adding a new column after this one: SUM(total_price)/COUNT( id) We're selecting all rows where the “created_at” column has a date� I am sharing my observations here: 1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME> Result Row Count: 508534 Console output: Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0.125 sec. Takes a while for a table with large number of rows, but the row count is very exact.

Comments
  • Whats the context for this? Are you just wanting to run this from SSMS manually while the insert is happening? Or something else?
  • Yes @DaleBurrell, exactly this, while the script is running, im on microsoft sql server management studio
  • And do you just want a single number since the last time you ran it? Or do you want a history?
  • yes, it could be a single number
  • You can always save a value, e.g. declare @Count1 as Int = ( select Count(*) from Foo ); for use later. Aside: @@RowCount is a handy way to get the number of rows affected by a statement, e.g. an insert.
  • Thanks @Lukasz, and sorry because i didn't include some informations: i'm in a production environment so im avoiding to create new temp tables. You know how to do this in another way?
  • Thank you @Gordon but didnt work, return "Must declare the scalar variable "@saved_id".