-1

my SQL code not working (syntax error at or near "FROM") i need my final table to be three rows. the average of standard_amt_usd for the three total group i created from Total column. where is my mistake?

select avg(orders.standard_amt_usd), orders.total
, case when orders.total between 1 and 100 then "1 to 100"
when orders.total between 101 and 300 then " 101 to 300"
else " +500 "

FROM orders;
group by total
Pythona
  • 9
  • 5
  • I don't know qsql but in SQL you shouldn't have a semicolon after FROM orders (because the command still has the GROUP BY), and your group by should also include the case when... expression (because it is not aggregated) – tinazmu Feb 01 '22 at 22:06
  • also, in general, strings are enclosed in single quotes, not double quotes (but it may be allowed in qsqlquery) – tinazmu Feb 01 '22 at 22:08

1 Answers1

2

Try using the END statement after all your cases.

Like this:

SELECT AVG(orders.standard_amt_usd), orders.total,
CASE
    WHEN orders.total BETWEEN 1 AND 100 THEN '1 to 100'
    WHEN orders.total BETWEEN 101 AND 300 THEN '101 to 300'
    ELSE '+500'
END AS OrderTotals
FROM orders
GROUP BY total;

Comments:

  • Always use single quotes (') with SQL not double (").
  • Your semicolon was at the end of the FROM statement. Put it after GROUP like I did above.

Feel free to refer to this

Nicolas Alkhoury
  • 31
  • 2
  • it is working now. but i need just three rows, the total avg for group one 1 to 100 and group 2 101 to 300 and +500 . – Pythona Feb 01 '22 at 22:31
  • You need to declare the ELSE statement each case and as Nicolas mentioned do not forget about the END statement. – Faizawa Feb 02 '22 at 01:13
  • it is not working. can you write what you mean? – Pythona Feb 02 '22 at 06:12
  • Be aware that @Pythona has many symptoms of a [help vampire](https://meta.stackoverflow.com/a/258279/4194079). Your answer is already good as is +1. – keepAlive Feb 02 '22 at 20:46