help making these sql statements work, please

mattvenables

Hi all -

I have several SQL statements, and I want to combine them into one. Here they are.... If anyone can help, that would be amazing.

SELECT COUNT(DISTINCT session) AS Event3Count
FROM table_name
WHERE page LIKE 'page_name'
AND event LIKE 3;

SELECT COUNT(DISTINCT session) AS Event4Count
FROM table_name
WHERE page LIKE 'page_name'
AND event LIKE 4;

In essence, I'm trying to count each event, but non-duplicates. So I grab the count of distinct session IDs where the event is like X.

It would be great to do this in one command, having each event separated so I can return Event3Count, Event4Count, etc.

Thanks
-Matt

tsinka

Hi,

event LIKE 3
is the same as
event='3'

Try this query:

SELECT event, COUNT(DISTINCT session) AS cnt 
FROM table_name 
WHERE page LIKE 'page_name' 
AND event IN (3,4)
GROUP BY event

This will return a resultset wich several rows, one row for each event and the count of distinct sessions as cnt for the given event.

Thomas

ccgorman

Not sure if this will work, but you may want to play about with GROUP BY

SELECT COUNT * AS EventCount
FROM table_name
WHERE page LIKE 'page_name'
AND event LIKE 3
GROUP BY session;

mattvenables

tsinka - thanks for the help... now for another quick add-on question

is there anyway that i could also have a field returned that would be EITHER event 3 or 4.

So one field is event 3 count, one is event 4 count, and another is eitehr event 3 or event 4. is that possible?

thank you!

tsinka

To be honest I didn't get exactly what you want to do. Give me an example of given data and how you want the result to look like.

Thomas

mattvenables

ok... i have a database with 3 columns: session, event, and page.

i want to count the total number of distinct users who commit event 3 and 4 while on page X.

You already solved that problem for me.

now i would like to know if the user committed either event 3 or 4. this isn't the same as just adding the two counts together because the user might have committed BOTH 3 and 4.

So, for example, say event 3 is clicking a button saying "sign up" and event 4 is clicking a button saying "refer a friend". I want the count of people who clicked EITHER signup or refer a friend. does that make sense?

thank you incredibly much.

tsinka

Ok,

I thought a little bit about that.

Try the following query:

SELECT event, COUNT(session) AS cnt  

FROM table_name  

WHERE page LIKE 'page_name'  

AND event IN (3,4) 
GROUP BY session
HAVING cnt=1

Execute the query and use mysql_num_rows on the result to get the number of rows. The number of rows returned should be the number of users that either had event 3 or event 4 in one session. This is done by the HAVING part because that would only return the rows where there is only one of the events for the given event ids.

In general selecting event with group by session doesn't make sense because the query would select one of the events (more or less randomly) and the count for all events.
But with HAVING cnt=1 this should work since you only select the rows with a count of 1 => only one event.

In order to sum up the counts for each event you need to walk through the result set and sum up counters depending on the event value.

Hope it works ...

Thomas

mattvenables

thank you very much for your help thomas.