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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

--=_alternative 006BFFAF85257057_=
Content-Type: text/plain; charset="US-ASCII"

m i l e s <magicmiles (AT) gmail (DOT) com> wrote on 08/08/2005 03:10:21 PM:


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

--=_alternative 006BFFAF85257057_=--

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:


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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Shawn,

Took me a bit to digest what you were sayin but if I get it the way
you splain'd it....
then the following should work:

++++++++++++++++++++++++++++++++++++++++++++++

SELECT
cmc_search_members.PropertyName AS pNAME,
cmc_search_members.PropertyEmail AS pEMAIL,
cmc_user_count.user_id,
MAX(cmc_user_count.date_time) AS pDATE,
COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID =
cmc_user_count.user_id
GROUP BY pNAME, pEMAIL
ORDER BY pDATE DESC

++++++++++++++++++++++++++++++++++++++++++++++

If I want a NON-CUMULATIVE result, ie:

pNAME + pHITS + pDATE
-------------------------------
fillmore + 198 + 08/08/2005
mannor inn + 56 + 08/08/2005
seacrest + 23 + 08/08/2005
-------------------------------
fillmore + 102 + 08/07/2005
mannor inn + 89 + 08/07/2005
seacrest + 19 + 08/07/2005

etc.

Then Im assuming the statement above will NOT produce this result ?

What'd be great is if I could get that in Alphabetical order as
well...I tried adding a
ORDER BY pDATE DESC, pNAME ASC but DESC doesn't help.

Which is what I was lookin for.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

--=_alternative 0075035185257057_=
Content-Type: text/plain; charset="US-ASCII"

m i l e s <magicmiles (AT) gmail (DOT) com> wrote on 08/08/2005 05:05:46 PM:

If you want your results broken down by dates, then you have to group on
some sort of date value. Try this:

SELECT
cmc_search_members.PropertyName AS pNAME,
cmc_search_members.PropertyEmail AS pEMAIL,
cmc_user_count.date_time AS pDATE,
COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID =
cmc_user_count.user_id
GROUP BY PDATE DESC, pNAME, pEMAIL;



For more details: http://dev.mysql.com/doc/mysql/en/select.html

If you use GROUP BY, output rows are sorted according to the GROUP BY
columns as if you had an ORDER BY for the same columns. MySQL has extended
the GROUP BY clause as of version 3.23.34 so that you can also specify ASC
and DESC after columns named in the clause:

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC

<<<<<<<<

That query will break down, by date, all of the hits for any pNAME+pEMAIL
combination. If we are not lucky enough that `cmc_user_count`.`date_time`
contains only date values but instead it contains dates+times then we need
to strip the time elements out in order to get just a daily grouping. It
would look something like this:

SELECT
cmc_search_members.PropertyName AS pNAME,
cmc_search_members.PropertyEmail AS pEMAIL,
DATE(cmc_user_count.date_time) AS pDATE,
COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID =
cmc_user_count.user_id
GROUP BY PDATE DESC, pNAME, pEMAIL;

(see http://dev.mysql.com/doc/mysql/en/da...functions.html for
other options)

Is this whole GROUP BY thing starting to make a little more sense now?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0075035185257057_=--