m i l e s <[EMAIL PROTECTED]> wrote on 08/08/2005 05:05:46 PM:
> 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.
>
>
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/date-and-time-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
