I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is higher than 1000, if not so, i need to return the count and if so, i can return -1.
CREATE TABLE [dbo].[Accounts]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](255) NOT NULL, [CreatedById] [bigint] NOT NULL, [CreatedDate] [datetimeoffset](7) NOT NULL, [ModifiedById] [bigint] NOT NULL, [ModifiedDate] [datetimeoffset](7) NOT NULL, [IsActive] [bit] NOT NULL, [StagingStatusId] [bigint] NULL, [IsApproved] [bit] NOT NULL, [IsActive] [bit] NOT NULL )
Currently, I have the following query that groups the data by the status and then returns the count.
select StagingStatusId, count(1) AS Total from I8Core.AccountCode where IsActive = 1 and IsApproved = 1 group by StagingStatusId
However, as the number of records in the database table is higher (> 100k), the query gets slower and delayed.
The aim of this query is to know what statusids are higher than 999 and what are lesser than 999.
For the records that are higher than 999 (Ex. if statusid = 1 & count = 1244, we need as statusid=1,count=999+), else, we need to return statusid=1, count=998 (for any value less than 999).
I found that the below query can return the total row count per table, but this cannot be applied to this scenario.
Kindly suggest the best practice to achieve this.