I need help to write linq syntax of below SQL query.
Business is: from table PhoneNumberClick_Table
, I need to get TotalCount
, CountWithWhereCondition
, and CountWithUniqueValue
datewise :
SELECT
FORMAT([ClickTime], 'yyyy-MM-dd') DateTimeField,
COUNT (*) CountTotalClick,
COUNT ((CASE PhoneNumber WHEN 'NotProvided' THEN 1 ELSE NULL END)) CountNumberProvided,
COUNT ((CASE PhoneNumber WHEN 'NotProvided' THEN NULL ELSE 1 END)) CountNumberNotProvided,
COUNT (DISTINCT PhoneNumber) CountUniqueNumber
FROM
[dbo].[PhoneNumberClick_Table]
GROUP BY
FORMAT([ClickTime], 'yyyy-MM-dd')
ORDER BY
FORMAT([ClickTime], 'yyyy-MM-dd')
use sum instead of count for case statement and your have to change the logic for phonenumber provided and not provided like my query
SELECT
FORMAT([ClickTime], 'yyyy-MM-dd') DateTimeField
,COUNT (*) CountTotalClick
,sum((case when PhoneNumber is not null then 1 else 0 end)) CountNumberProvided
,sum((case when PhoneNumber= 'NotProvided' then 0 else 1 end)) CountNumberNotProvided
,COUNT (DISTINCT PhoneNumber) CountUniqueNumber
FROM [dbo].[PhoneNumberClick_Table]
GROUP by FORMAT([ClickTime], 'yyyy-MM-dd')
ORDER BY FORMAT([ClickTime], 'yyyy-MM-dd')