Mysql join using date between

Question

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

Answer 1

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

Answer 2

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.