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