m i l e s <[EMAIL PROTECTED]> 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
