-1

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.

Ivy
  • 1
  • 3
    (1) Tag with the database you are using. (2) Explain what you are trying to accomplish. Your `having` clause doesn't make sense to me either. (3) What is `t1`? – Gordon Linoff Sep 01 '21 at 21:35
  • Only subqueries in the FROM list can be given aliases. – jarlh Sep 01 '21 at 21:37
  • Can you describe in words what your intentions are with the query? – Stu Sep 01 '21 at 21:57
  • @jarlh: and of course - those in the `JOIN` clauses, too - if there are any – marc_s Sep 02 '21 at 03:55
  • @marc_s, the JOIN clauses are parts of the FROM clause. – jarlh Sep 02 '21 at 06:50
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Sep 05 '21 at 09:43

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786