I have a table with columns: cid, date
Sample table data: Note: cid contains string values eg: 'otsytb8o7sbs50w9doghwzvfy0vb8f9h' many are duplicated.
cid. date
--------------------------------------------------------
1 2015-10-10 04:57:57
2 2015-10-10 05:03:58
3 2015-10-10 05:24:49
4 2015-10-10 05:28:24
5 2015-10-10 05:28:26
6 2015-10-10 05:28:40
7 2015-10-10 05:30:39
8 2015-10-10 05:33:04
9 2015-10-10 05:35:42
9 2015-10-10 05:36:03
I want to get the following:
cid
as uniqVisitscid
HAVING (count <= 1) as bouncedI want to get bounce rate per month from Cookie ID's (cid
).
So I am looking for: ( COUNT of unique Cookie ID's with a count of <=1 ) for bounced, and ( COUNT DISTINCT cid's ) for total unique visitors, Grouped By month
Desired result:
uniqVisits | bounced | month
-----------|---------|-------
2345 | 325 | 2015-10
-----------|---------|-------
7345 | 734 | 2015-11
-----------|---------|-------
3982 | 823 | 2015-12
-----------|---------|-------
4291 | 639 | 2016-01
I have tried a lot of methods the below is the closest I can get but it gives me error: "Operand should contain 1 column(s)"
SELECT count(*) AS bounced,
( SELECT count( DISTINCT(cid) ) AS uniqVisits,
SUBSTR(DATE(date),1,7) AS month
FROM table ) AS uniqVisits
FROM (
SELECT COUNT(cid) AS bounced,
SUBSTR(DATE(date),1,7) AS month
FROM table
GROUP BY cid
HAVING (count <= 1)
) AS x
GROUP BY month
How can I write this query to give me the desired result I want in the "Desired result:" chart / table illustrated above?
BTW: I also tried the below query but it times out, and then throws a server error: It also does not group the second query into month, obviously because of the "cid having count <=1"
SELECT c1.uniqVisits,
c1.month,
c2.bounced
FROM ( SELECT COUNT(DISTINCT t1.cid) AS `uniqVisits`,
SUBSTR(DATE(t1.date),1,7) AS `month`
FROM table t1
GROUP BY month
) c1
JOIN ( SELECT COUNT(*) AS `bounced`,
SUBSTR(DATE(t2.date),1,7) AS `month`
FROM table t2
GROUP BY month, cid HAVING (count <= 1)
) c2
ON c2.month = c1.month
ORDER BY c1.month
So I have resolved this:
SELECT uniqVisitors, COUNT(*) AS bounced, T1.month
FROM (
SELECT cid,
SUBSTR(DATE(date),1,7) AS month
FROM table
GROUP BY cid
HAVING COUNT(*) <= 1
) T1
LEFT JOIN
( SELECT count( DISTINCT(cid) ) AS uniqVisitors,
SUBSTR(DATE(date),1,7) AS month
FROM table
GROUP By month ) T2
ON T1.month = T2.month
GROUP BY month
Gives me:
uniqVisitors | bounced | month
---------------------------------
7237 6822 2015-10
12597 12136 2015-11
12980 12573 2015-12
12091 11695 2016-01
5396 5134 2016-02
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With