Grouping a Subselect
Posted on 2003-03-03
I've got a problem with the following query:
SELECT ( DatePart(HH, fld_date) ) AS myHour,
COUNT(fld_ID) AS numPageviews,
COUNT(DISTINCT fld_visitor_ID) AS numSessions,
COUNT(DISTINCT fld_page_ID) AS numPages,
COUNT(DISTINCT fld_statistic_fuseaction_ID) AS numFuseactions,
SUM(fld_authenticated) AS numAuth
FROM tbl_statistic_data main
WHERE fld_date >= #CreateODBCDateTime(startDate)#
AND fld_date <= #CreateODBCDateTime(endDate)#
AND fld_site_ID = #session.sid#
GROUP BY DatePart(HH, fld_date)
ORDER BY DatePart(HH, fld_date)
The query returns statistical data grouped by hour.
The problem is with COUNT(DISTINCT fld_statistic_fuseaction_ID) AS numFuseactions.
I want it to count only those records where fld_page_ID is NULL. I believe this could be achieved with a subselect, but I don't know how to "attach" the subselect to my main query groups so it doesn't return the count for the entire timespan every time, but for every single hour.
Is there a way to achieve this or do I need to write multiple queries?