How Do I Group a MySQL Query Together? 1

[3dColor]

I am tracking two different pages when a person leaves my site and signs up with a merchant partner.

When the person leaves my site to the merchant's landing form page I enter information about that person in my database table called 'tracking'.

When that person successfully fills out the form on the merchant site a few minutes later I use a tracking pixel on the success/thankyou page to also enter info about that person.

Both hits are stored to the same table called 'tracking'. The only difference is on the event that the tracking pixel is fired it places a '1' in the 'thankyou' column.

What I want to do is then group these two events by matching the ipAddress, browser info and the day it happened.

67.222.333.44 | Chrome browser blah, blah | 2011-02-08 10:38:15 | thankyou=0
67.222.333.44 | Chrome browser blah, blah | 2011-02-08 10:41:46 | thankyou=1

But I am not sure how to go about writing the query so I can output the query correctly. All I want to return is one record per group.

<!--- Get matching IP, date range and browser info --->
<cfquery name="GetSuccessLeads" datasource="#DSN#" >
   	SELECT		cs, browserinfo, thankyou, affCompany, amount, referrer, ipAddress, timeStamp
	FROM 		tracking
    WHERE 		thankyou = '1'
   	GROUP BY	timeStamp,
    			browserinfo,
                timeStamp
</cfquery>

<cfoutput query="GetSuccessLeads">
    #ipaddress#, #browserinfo#, #timeStamp#, #amount#, #cs#, #referrer#<br >
</cfoutput>

 

[r937]

... group these two events by matching the ipAddress, browser info and the day it happened.

you need to bring your GROUP BY clause into line with this

SELECT MAX(cs) AS cs
     , browserinfo
     , MAX(thankyou) AS thankyou
     , MAX(affCompany) AS affCompany
     , MAX(amount) AS amount
     , MAX(referrer) AS referrer
     , ipAddress
     , DATE(timeStamp) AS thedate 
  FROM tracking   
GROUP 
    BY ipAddress
     , browserinfo
     , thedate

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[3dColor]

Wow! Thanks once again Rudy. You are the man! Looks like you got up early this morning.

 

[3dColor]

Rudy,

I thought it was working when i first tried it but now since I have a lot of data now it does not return the correct recordset.

It is returning 59 out of the 64 records in the table.

I only have only 1 successful thankyou (1) page accessed so I should only see 1 record returned.

I just want to group all the records that have matching IPaddress, Date and BrowserInfo. In the table there are only two that match all three:

67.222.333.44 | Chrome browser blah, blah | 2011-02-08 10:38:15 | thankyou=0
67.222.333.44 | Chrome browser blah, blah | 2011-02-08 10:41:46 | thankyou=1

For some reason it is returning way too many records, any ideas on how to return just 1 record with all the info in it?

Dave

 

[r937]

sorry, i don't understand the issue

I just want to group all the records that have matching IPaddress, Date and BrowserInfo.

to meet this requirement, you should use

GROUP     
    BY ipAddress
     , browserinfo
     , thedate

note that GROUP BY does not return "records" but rather, aggregate rows which consst of multiple table rows collapsed into one

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon