Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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:

  1. Count of Distinct cid as uniqVisits
  2. Count of cid HAVING (count <= 1) as bounced
  3. Grouped by month

I 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
like image 493
Grant Avatar asked Feb 14 '16 09:02

Grant


1 Answers

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
like image 72
Grant Avatar answered Nov 03 '22 11:11

Grant