Sql Query not working?

Question

Why this query is not returning data of 2011 and 2012. Can anyone help me out.Thanks in advance.

SELECT 
    country_code,
    SUM(`attendance`) as k6_attendance,
    count(*) as total_events ,
    IF(MONTH(`session_date`)<5,YEAR(`session_date`),YEAR(`session_date`)+1) as YR2
  FROM `v_knowledge_session` v
  WHERE  session_date >0 
  Group by YR2,country_code
  HAVING YR2>(YEAR(NOW())-2) AND country_code='IN'
  ORDER BY country_code,YR2;

Post some of the records for `session_date` and `country_code` from the table. — Ravinder Reddy, Jul 16 '12 at 06:07
provide your table structure also.. means description of your table.. — pratik garg, Jul 16 '12 at 06:08
Are you sure you have data with country_code='IN' *and* session_date in the past 2.5 years? Can you post a table description (`DESCRIBE v_knowledge_session;` and some sample data and expected output? — Chris Trahey, Jul 16 '12 at 06:13

Zane Bien · Answer 1 · 2012-07-16T06:34:07.443

Try wrapping it in a subselect, then making the comparison on YR2 afterwards in the outer query:

SELECT a.* 
FROM
(
    SELECT 
        country_code,
        SUM(attendance) AS k6_attendance,
        COUNT(*) AS total_events,
        IF(MONTH(session_date)<5,YEAR(session_date),YEAR(session_date)+1) AS YR2
    FROM v_knowledge_session v
    WHERE session_date > 0 AND country_code = 'IN'
    GROUP BY YR2, country_code
) a
WHERE 
    a.YR2 > YEAR(NOW())-2
ORDER BY 
    a.country_code, a.YR2

score 0 · Answer 2 · answered Jul 16 '12 at 06:08

You need to use the same expression in places other than ORDER BY clause

SELECT country_code,SUM(`attendance`) as k6_attendance,count(*) as total_events , 
    IF(MONTH(`session_date`)<5,YEAR(`session_date`),YEAR(`session_date`)+1) as YR2 
    FROM `v_knowledge_session` v 
    WHERE  session_date >0  
    Group by IF(MONTH(`session_date`)<5,YEAR(`session_date`),YEAR(`session_date`)+1) ,country_code 
    HAVING IF(MONTH(`session_date`)<5,YEAR(`session_date`),YEAR(`session_date`)+1) >(YEAR(NOW())-2) AND country_code='IN' 
    ORDER BY country_code,YR2;

Use of column or expression *aliases* in `having` is acceptable. Refer [*here*](http://www.mysqltutorial.org/mysql-having.aspx). — Ravinder Reddy, Jul 16 '12 at 06:12

Ambrose · Answer 3 · 2012-07-16T06:21:45.830

You can't use non-aggregated condition in HAVING clause. country_code='IN' must be in the WHERE clause. The reason why HAVING clause was created is because the WHERE clause cannot handle aggregated condition. In your query, having clause is not needed because your condition is not aggregated. Also you can remove the ALIAS v

try this one:

SELECT    country_code, 
          SUM(`attendance`) as k6_attendance,
          count(*) as total_events ,
          IF(MONTH(`session_date`) < 5 ,
               YEAR(`session_date`),
               YEAR(`session_date`) + 1) as YR2
FROM      `v_knowledge_session` 
WHERE      session_date > 0 AND country_code='IN' AND YR2 > (YEAR(NOW())-2) 
Group by  YR2, country_code
ORDER BY  country_code,YR2;

No. Use of column or expression *aliases* in `having` is acceptable, but not in `where` clause. Refer [*here*](http://www.mysqltutorial.org/mysql-having.aspx), [*here*](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) and at [*Problems with Column Aliases*](http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html) too. — Ravinder Reddy, Jul 16 '12 at 06:16

Sql Query not working?

3 Answers3