List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 8 2005 7:35pm
Subject:Re: Query HELP!
View as plain text  
m i l e s <magicmiles@stripped> wrote on 08/08/2005 03:10:21 PM:

> 
> Hi,
> 
> IS the following query counting cumulative (see below **) pHITS or is 
> it counting individual counts for each user for a particular day.  Im 
> not skilled enough to answer this question myself.  My instinct says 
> that its counting cumulative values and NOT individual counts for 
> each property name.
> 
> SELECT
> search_members.Property_Name AS pNAME,
> search_members.Property_Email AS pEMAIL,
> MAX(user_count.u_datetime) AS pDATE,
> **COUNT(user_count.u_userid) AS pHITS
> FROM search_members INNER JOIN user_count ON 
> search_members.Property_ID = user_count.u_userid
> GROUP BY pNAME, pEMAIL, search_members.Property_ID
> ORDER BY pDATE DESC
> 
> I have a feeling that the COUNT line should be something similar to 
> select distinct statement....
> 
> Any ideas ?
> 
> Sincerely,
> 
> M i l e s.
> 


Your original query,reformatted for explanatory purposes:

SELECT 
        search_members.Property_Name AS pNAME,
        search_members.Property_Email AS pEMAIL,
        MAX(user_count.u_datetime) AS pDATE,
        COUNT(user_count.u_userid) AS pHITS
FROM search_members 
INNER JOIN user_count 
        ON search_members.Property_ID = user_count.u_userid
GROUP BY 
        pNAME
        , pEMAIL
        , search_members.Property_ID
ORDER BY pDATE DESC

You wanted to know what this query is calculating, right?

Let's look first at your select terms: terms 1 and 2 are direct field 
values, terms 3 and 4 are the results of aggregate functions.

Now let's look at what you are grouping by:  pNAME, pEMAIL, and 
search_members.Property_id.

if you had done a GROUP BY *only on* pNAME and pEMAIL, you would have seen 
each pair of values appear only once in your output along with the last 
time they did *something* (not sure what it was), the pDate value,  and 
how many times they did it, the pHITS value. However you are also 
computing those statistics _ for each property_ID _. So if the same 
pNAME+pEMAIL pair had performed whatever it was they had to do to generate 
some pDate and pHITS values for more than one Property_ID value, then you 
will see one pNAME+pEMAIL pair listed for EACH PROPERTY ID to which the 
statistics apply.

One way to see this more clearly is to add the Property_ID column into the 
SELECT portion of your query.

SELECT 
        search_members.Property_Name AS pNAME,
        search_members.Property_Email AS pEMAIL,
        search_members.Property_ID,
        MAX(user_count.u_datetime) AS pDATE,
        COUNT(user_count.u_userid) AS pHITS
FROM search_members 
INNER JOIN user_count 
        ON search_members.Property_ID = user_count.u_userid
GROUP BY 
        pNAME
        , pEMAIL
        , search_members.Property_ID
ORDER BY pDATE DESC

Now you should be able to physically "see" why the pNAME+pEMAIL pairs were 
duplicating in what may have appeared to be randomly and without reason. I 
am not sure if I answered your question but this seemed like the most 
likely issue for confusion.

Is it cumulative? Yes. Is it per day? No. Is it per 
Name+Email+Property_ID? Yes.

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Thread
Query HELP!m i l e s8 Aug
  • Re: Query HELP!SGreen8 Aug
    • Re: Query HELP!m i l e s8 Aug
      • Re: Query HELP!SGreen8 Aug
  • Re: Query HELP!Peter Brawley8 Aug