asked on

Grouping a Subselect

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?

Thanks, Martin

malekam

Sure,
Just change

COUNT(DISTINCT fld_statistic_fuseaction_ID as numFueseactions

to:

SUM(CASE fld_statistic_fuseaction_ID
WHEN NULL THEN 1
ELSE 0
END) AS numFueseactions

This counts all of the null id's. You don't need the distinct since, if the id is null, it will not be distinct.

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knobbo

ASKER

Thanks lowfatspread, your first solution is exactly what i wanted.
I'll try the other one as well, might come in handy later :)