Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

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
0
knobbo
Asked:
knobbo
1 Solution
 
malekamCommented:
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.
0
 
LowfatspreadCommented:

change
COUNT(DISTINCT fld_statistic_fuseaction_ID) AS numFuseactions

to Count(Distinct Case when fld_page_ID is NULL then fld_statistic_fuseaction_ID else Null end) AS numFuseactions


or is this more like what you're after

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 sub.subfuse) AS  numFuseactions,
              SUM(fld_authenticated) AS numAuth
    FROM     tbl_statistic_data main
     left outer Join
       (Select Datepart(hh,fld_date) as SubHr,
               Count(distinct
                 fld_statistic_fuseaction_id)  as SubFuse
          from tbl_statistic_data
    WHERE     fld_date >= #CreateODBCDateTime(startDate)#
    AND      fld_date <= #CreateODBCDateTime(endDate)#
    AND      fld_site_ID = #session.sid#
    and page_id is null
    GROUP BY DatePart(HH, fld_date)
        ) as Sub
    on Datepart(hh, main.fld_date) = sub.subhr
    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)



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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now