Mysql查询帮助 - 计算行数

Question

I have two different tables trackingevent and link

Trackingevent looks like

eventName|eventStamp
SHARE_FACEBOOK|2011-01-20 14:05:40
SHARE_TWEET|2011-01-20 14:47:57
SHARE_FLICKR|2011-01-20 15:08:58
SHARE_STATION_LOGIN|2011-01-20 15:09:09
EMAIL_SHARE|2011-01-20 15:10:13
CONTEST_ENTRY:BLAH DATA|2011-01-20 15:10:13
CONTEST_ENTRY:BLAH DATA|2011-01-20 15:10:13

and link looks like

id|emailSub
6|1
7|0
8|1
9|0

And what I need to do is I need to count all the SHARE_FACEBOOK and all the SHARE_TWEET and all the SHARE_FLICKR and all the SHARE_STATION_LOGIN and all the COPNTEST_ENTRIES (Without the rest of the data after the :) AND EMAIL_SHARE and somehow combine those with the amount of emailSub (equals to 1) and amount of id.

So I get a returned array sort of like

EMAIL_SHARE     77
SHARE_FLICKR    9
SHARE_FACEBOOK  105
SHARE_STATION_LOGIN     223
SHARE_TWEET     18
# of ID's
# of emailsub=1
CONTEST_ENTERIES 550

I can get the first part of it using SELECT eventName, COUNT(*) FROM trackingevent GROUP BY eventName But I am confused with how to get the # of ID's in the link the # of emailsubs=1 and the number of CONTEST_ENTRY:BLAH DATA (the blah data changes).

Would I have to do three different sql queries and combine the data? Or could I somehow combine them into a single one or?

Answer 1

This should give you the count of each type using the prefix

SELECT LEFT(eventName,INSTR(CONCAT(eventName,':'),':')) as prefix, count(*)
FROM trackingevent
GROUP BY LEFT(eventName,INSTR(CONCAT(eventName,':'),':'))

You'll have to answer the questions in the comments if this isn't what you were asking for