CODEWITHSUNDEEP
mysqlsql
jaya
jaya

Reputation: 327

Correct my MySQL query?

I have table like 

CREATE TABLE `survey` (
  `id` int(11) NOT NULL auto_increment,
  `submitdate` datetime default NULL,
 `answer` varchar(5) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=499 ;

now to get values like 

    c                t         Clicks   
2012-10-29  2012-10-22       10
2012-11-04  2012-10-30       20
2012-11-11  2012-11-05       30
2012-11-19  2012-11-12       34

I am using this query 

SELECT uq.timespan, COALESCE(tsq.TotalClicks, 0) as Clicks FROM (
SELECT DATE( DATE_ADD( NOW( ) , INTERVAL -21
DAY ) ) c, DATE( DATE_ADD( NOW( ) , INTERVAL -28
DAY ) ) l
union SELECT DATE( DATE_ADD( NOW( ) , INTERVAL -15
DAY ) ) c, DATE( DATE_ADD( NOW( ) , INTERVAL -20
DAY ) ) l
union SELECT DATE( DATE_ADD( NOW( ) , INTERVAL -8
DAY ) ) c, DATE( DATE_ADD( NOW( ) , INTERVAL -14
DAY ) ) l
union SELECT curdate() c,DATE( DATE_ADD( NOW( ) , INTERVAL -7
DAY ) ) l
)uq LEFT JOIN (
SELECT CASE 
    WHEN submitdate >= NOW() - INTERVAL 4 WEEK
                AND submitdate < NOW() - INTERVAL 3 WEEK THEN  c 'to' l
DAY ) )
    WHEN submitdate >= NOW() - INTERVAL 3 WEEK
                AND submitdate < NOW() - INTERVAL 2 WEEK THEN c 'to' l
    WHEN submitdate >= NOW() - INTERVAL 2 WEEK
        AND submitdate < NOW() - INTERVAL 1 WEEK THEN c 'to' l
DAY ) )
    WHEN submitdate >= NOW() - INTERVAL 1 WEEK THEN c 'to' l
           END Weeksubmitdate, 
           count(id) TotalClicks
FROM survey
WHERE submitdate >= NOW() - INTERVAL 4 WEEK
GROUP BY Weeksubmitdate
)tsq ON uq.timespan = tsq.Weeksubmitdate";

problem is with 16th line c to l.

I am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''to' l DAY ) ) WHEN submitdate >= NOW() - INTERVAL 3 WEEK ' at line 16

Upvotes: 0

Views: 126

Answers (1)

Andriy M
Andriy M

Reputation: 77657

CASE is supposed to evaluate to a scalar expression. That means its THEN clauses must evaluate to scalar expressions too. Now, what does this c 'to' l thing stand for? Is it a scalar expression? It doesn't seem one to me, however I may be unaware of some things in MySQL, so it's more important whether MySQL itself recognises that as a scalar expression. And apparently it doesn't.

There is another issue. You are trying to reference a derived table's columns inside another derived table. More specifically, you seem to be trying to reference the columns c and l of uq inside the tsq subselect, and that is illegal. If uq was a normal table, it would be fine, but since it is a virtual table, the query doesn't know about its existence at that point, i.e. at the time of parsing the tsq subquery.

Anyway, what you seem to be doing with your query could probably be rewritten more simply, like this, for instance:

SELECT
  MIN(submitdate) AS startdate,
  MAX(submitdate) AS enddate,
  COUNT(*) AS clicks
FROM (
  SELECT
    CASE
      WHEN submitdate >= NOW() - INTERVAL 1 WEEK THEN 1
      WHEN submitdate >= NOW() - INTERVAL 2 WEEK THEN 2
      WHEN submitdate >= NOW() - INTERVAL 3 WEEK THEN 3
      WHEN submitdate >= NOW() - INTERVAL 4 WEEK THEN 4
    END AS weekid,
    *
  FROM survey
) s
GROUP BY
  weekid
ORDER BY
  startdate
;

The subquery assigns surrogate week IDs to every row of survey. The main query groups the results by those IDs and produces the counts as well as starting & ending dates for every group.

Upvotes: 1

Related Questions