query with agrregate function

Question

The table structure like this

Cr_By Date

1 2014-09-09 04:40:57.463

1 2014-09-09 04:55:10.700

sSql = " SELECT *FROM WF_Dailyreport WHERE DATEPART(yyyy, WF_CrOn) = " & year & " AND DATEPART(mm, WF_CrOn) = " & month & " AND WF_CrBy=" & crby & ""

If i select the user and month and year for above query i am getting both rows

but i need count as 1

how to get that count?..

please tell me..

Sasi Reddy · Answer

I am using this query SELECT COUNT(distinct( SELECT CONVERT (Date WF_CrOn))) FROM WF_Dailyreport WHERE DATEPART(yyyy WF_CrOn) = 2014 AND DATEPART(mm WF_CrOn) = 9 AND WF_CrBy=1 I am getting this below error Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Ravi Kumar · Answer

hi check fallowing SELECT WF_Cron COUNT(*) TotalCount FROM WF_Dailyreport GROUP BY WF_Cron HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

Sasi Reddy · Answer

Hi I need count for distinct(WF_Cron)

Ravi Kumar · Answer

hi check the fallowing sSql =  SELECT *FROM WF_Dailyreport WHERE year(WF_CrOn) =  & year &  AND Month(WF_CrOn) =  & month &  AND WF_CrBy= & crby &