Geek Answers Handbook

COUNT DISTINCT + COUNT GROUP BY HAVING (value) + GROUP BY months

I have a table with columns: cid, date

Example table data: Note: cid contains string values, for example: '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:

  • Count of Distinct cid as uniqVisits
  • Number cid HAVING (count <= 1) as a rebound
  • Grouped by Month

I want to get bounce rate per month from cookie ID ( cid ).

So I'm looking for: (COUNT unique cookie ID with number <= 1) for a rebound and (COUNT DISTINCT cid) for all 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 many methods that are best suited, but this gives me an error: "The operand must contain 1 column"

 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 that I want in the "Desired result:" diagram shown above?

BTW: I also tried the following request, but it expires and then gives a server error: it also does not group the second request per month, obviously due to "cid with 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
+6
source share
1 answer

So, I solved 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
0
source

More articles:


All Articles