CODEWITHSUNDEEP
mysqlsql
vis
vis

Reputation: 773

Mysql join using date between

I have one table analytics_metrics. i am trying to get count from visitorsStatistics and pageviewsStatistics for the last x days. the date range can change. 

id  metrics             count   date
67  visitorsStatistics  15779   2013-10-10
69  pageviewsStatistics 282141  2013-10-10
90  visitorsStatistics  14588   2013-10-11
92  pageviewsStatistics 265042  2013-10-11
108 pageviewsStatistics 278523  2013-10-12
106 visitorsStatistics  15015   2013-10-12
122 visitorsStatistics  16474   2013-10-13
124 pageviewsStatistics 312752  2013-10-13
138 visitorsStatistics  16829   2013-10-14
140 pageviewsStatistics 320614  2013-10-14
85  pageviewsStatistics 67976   2013-10-15
83  visitorsStatistics  5452    2013-10-15

i am looking to get an output like this :

visitorsStatistics   pageviewsStatistics
15779                282141 
14588                265042 
15015                278523 
16474                312752 
16829                320614 
5452                 67976

i have tryed different queries for more than 4 hours now i just cant seem to find the right way to do it :-(.

here is what i got so far:

SET @fromDate = '2013-10-10'; 
set @tillDate = '2013-10-11';

SELECT 
  *
   /* ga_visits.count as visits,
    ga_pageviews.count as pageviews
*/
 FROM analytics_metrics as ga_visits
    LEFT JOIN analytics_metrics as ga_pageviews on (ga_pageviews.date BETWEEN @fromDate AND @tillDate AND ga_pageviews.metrics = 'pageviewsStatistics')
    WHERE ga_visits.date BETWEEN @fromDate AND @tillDate AND ga_visits.metrics = 'visitsStatistics'

if i use this query for one day it works fine but not for a date range.

hope someone can help.

thank you in advance

Upvotes: 3

Views: 1840

Answers (4)

Alma Do
Alma Do

Reputation: 37365

If I got that correctly, you want to combine paired rows within one date, like:

SELECT
  l.count AS visitorsStatistics,
  r.count AS pageviewsStatistics
FROM
  (SELECT * FROM analytics_metrics WHERE metrics='visitorsStatistics') AS l
  LEFT JOIN
  (SELECT * FROM analytics_metrics WHERE metrics='pageviewsStatistics') AS r
    ON l.date=r.date
WHERE
  l.date BETWEEN @fromDate AND @tillDate

-see this fiddle

Upvotes: 1

Praveen D
Praveen D

Reputation: 2377

Try this

SELECT 
  *
   /* ga_visits.count as visits,
    ga_pageviews.count as pageviews
*/
 FROM analytics_metrics as ga_visits
    LEFT JOIN analytics_metrics as ga_pageviews on (ga_pageviews.date BETWEEN @fromDate AND @tillDate AND ga_pageviews.metrics = 'pageviewsStatistics')
    WHERE ga_visits.date BETWEEN DATE_ADD(fromDate, INTERVAL 1 DAY) and DATE_SUB(tillDate, INTERVAL 1 DAY) AND ga_visits.metrics = 'visitsStatistics'

Upvotes: 0

Sanal K
Sanal K

Reputation: 733

Check like this. 

SELECT if(metrics='visitorsStatistics',count) AS visitorsStatistics,if(metrics='pageviewsStatistics',count) AS pageviewsStatistics
    FROM metrics_table
    WHERE date BETWEEN '2013-10-10' AND '2013-10-15'
    GROUP BY date

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this out:

SELECT
  sum(if(metrics = 'visitorsStatistics', `count`, 0)) visitorsStatistics,
  sum(if(metrics = 'pageviewsStatistics', `count`, 0)) pageviewsStatistics
FROM analytics_metrics am
WHERE <WHATEVER YOU NEED>
GROUP BY `date`

Fiddle here.

Upvotes: 1

Related Questions