SQL Count entries with value per Date-CodePudding

I have a table in SQL Server:

CREATE TABLE healthRegistration
(
    ID uniqueidentifier PRIMARY KEY,
    RegisterDateTime DateTime NOT NULL,
    RUFeelingWell Bool NOT NULL
);

The RegisterDateTime is the date and time that the user created the register and RUFeelingWell is a boolean value.

I want to create a query that returns 3 columns: Register Date, Count of entries with RUFeelingWell = False on that date, Count of entries with RUFeelingWell = True on that date

How can I do this?

CodePudding user response：

SELECT CAST(T.RegisterDateTime AS DATE)REGISTERDATE,
SUM
(
  CASE
   WHEN T.RUFeelingWell =1 THEN 1
   ELSE 0
  END
)AS TRUE_RECORDS,
SUM
( CASE
   WHEN T.RUFeelingWell =0 THEN 1
   ELSE 0
  END
)AS FALSE_RECORDS
FROM healthRegistration AS T
GROUP BY CAST(T.RegisterDateTime AS DATE)

CodePudding user response：

Try this query:

Select 
Convert(char(8), RegisterDateTime, 112), 
Sum(Case RUFeelingWell When 1 Then 1 Else 0 End) As wellnos,
Sum(Case RUFeelingWell When 0 Then 1 Else 0 End) As notwellnos
From healthRegistration
Group By Convert(char(8), RegisterDateTime, 112)
Order By Convert(char(8), RegisterDateTime, 112)

Here Convert(char(8), RegisterDateTime, 112) gives date in YYYYMMDD format so that entries for a day are summed together. Boolean values are stored as 1 and 0 in the database