SQL: Syntax Error after Having (Select ...) table

Question

The question is to extract information about customer that spent the most (in total over their lifetime as a customer) total_amt_usd, and COUNT the web_events they had for each channel.

I am trying to create an extra table from original table, and both of tables are needed to extract information. However, I keep getting syntax error and unable to proceed further.

SELECT t1.acc, COUNT(w.channel) total,
sum(case when w.channel = 'direct' then 1 else 0 end) direct,
sum(case when w.channel = 'adwords' then 1 else 0 end) adwords,
sum(case when w.channel = 'banner' then 1 else 0 end) banner,
sum(case when w.channel = 'facebook' then 1 else 0 end) facebook,
sum(case when w.channel = 'organinc' then 1 else 0 end) organic,
sum(case when w.channel = 'twitter' then 1 else 0 end) twitter
FROM web_events w
HAVING (
    SELECT o.account_id acc, SUM(o.total_amt_usd) total
    FROM orders o
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 1
) t1
GROUP BY t1.acc

The syntax error is near at 't1' at the end of table after Having and before group by.

Answer 1

If you want to include only accounts with orders -- my best guess as to what you might be attempting -- I would suggest a where clause with exists:

SELECT w.account_id, COUNT(w.channel) total,
       sum(case when w.channel = 'direct' then 1 else 0 end) as direct,
       sum(case when w.channel = 'adwords' then 1 else 0 end) as adwords,
       sum(case when w.channel = 'banner' then 1 else 0 end) as banner,
       sum(case when w.channel = 'facebook' then 1 else 0 end) as facebook,
       sum(case when w.channel = 'organinc' then 1 else 0 end) as organic,
       sum(case when w.channel = 'twitter' then 1 else 0 end) as twitter
FROM web_events w
WHERE EXISTS (SELECT 1
              FROM orders o
              WHERE o.account_id = w.account_id
             )
GROUP BY w.account_id;