From: Peter Brawley
Date: August 8 2005 8:00pm
Subject: Re: Query HELP!
List-Archive: http://lists.mysql.com/mysql/187518
Message-Id: <42F7B9D5.2050109@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Miles,

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

Is that query a bit confused, or is it me? If there's just one 
Property_Name and one Property_Email per property_Id in the 
search_members table, there's not a need to GROUP BY pname and pemail, 
and the query seems intended to return the latest user_count.u_datetime 
and the count of non-null values of user_count.u_userids for every 
search_members.property_Id. If there can be multiple names & emails per 
property_ID, the query will break down the counts by name, email then 
property_id, which would seem bizarre :-) .

PB



m i l e s wrote:

>
> 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.
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005