Subtracting count of two different columns-CodePudding

I have the following table

Table A
UploadDate  Destroy
12/23/2020   1
12/31/2025   0
11/11/2020   1
12/16/2021   1

I have aroud 1000 rows like this. I need to write a SQL statement where count(uploadDate) - count of destroy where destroy=1 so in the above case, it will be 4-3. I want 1 to be returned by the query. Bedlow is the create table statement:

CREATE TABLE [dbo].[Table_A](
    [UploadDate] [datetime] NULL,
    [Destroyed] [bit] NULL
) ON [PRIMARY]
GO

below are the insert statement for some sample data:

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/23/2020'
           ,1)



INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/22/2020'
           ,0)

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/31/2020'
           ,0)
        

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('11/11/2020'
           ,1)


         INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/16/2021'
           ,1)

This is what I have so far:

select count(uploadDate) - count(Destroyed)
from document
where destroyed=1

CodePudding user response：

You may use COUNT(CASE WHEN destroyed = 1 THEN 1 END) to find the count of rows where destroyed = 1.

SELECT COUNT(*) - 
       COUNT(CASE WHEN destroyed = 1 THEN 1 
END) AS RES
FROM Table_A

See a demo from db<>fiddle.