List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 8 2005 9:14pm
Subject:Re: Query HELP!
View as plain text  
m i l e s <magicmiles@stripped> 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
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