How to solve this particular query?

SELECT    id,  count(*) as Number
FROM     (SELECT id  FROM t1
            UNION ALL
          SELECT id  FROM t2
            UNION ALL
          SELECT id  FROM t3
         ) t
GROUP BY  id
ORDER BY  Number DESC

This is the query giving me the correct result. But When I want to add where call it is throwing error.

SELECT    id, count(*) as Number
FROM     (SELECT id from t1
            UNION ALL
      开发者_运维问答    select id from t2
            UNION ALL
          select id from t3
         ) t
WHERE     Number > 10 
GROUP BY  id
ORDER BY  Number DESC

You want to test conditions on an aggregate function with a HAVING clause rather than a WHERE.

select id, count(*) as Number 
    from (select id 
              from t1 
          UNION ALL 
          select id 
              from t2 
          UNION ALL 
          select id 
              from t3) t 
    group by id
    having Number > 10
    order by Number desc;

select
    id,
    count(*) as Number
from
    (
    select
        id
    from
        t1
    UNION ALL
        select
            id
        from
            t2
        UNION ALL
            select
                id
            from
                t3
    )t
group by
    id
HAVING
    Number > 10
order by
    Number desc;

Try that - I think its easier to debug if you make your query easy to read...