SQL Newbie

Can’t seem to figure why I am getting an error in my (very simple) code:

SELECT *, order_total AS Net_Revenue,
CASE
WHEN ‘Net_Revenue’ > 0 THEN ‘$0-$9.99’,
WHEN ‘Net_Revenue’ > 9.99 THEN ‘$10-$19.99’,
WHEN ‘Net_Revenue’ > 19.99 THEN ‘$20-$29.99’,
WHEN ‘Net_Revenue’ > 29.99 THEN ‘$30-$39.99’,
WHEN ‘Net_Revenue’ > 39.99 THEN ‘$40-$49.99’,
WHEN ‘Net_Revenue’ >= 50 THEN ‘$50+’,
END AS ‘Bucketed_Net_Revenue’
FROM “Orders”;

1 Like
  1. SELECT * indicates that you want to retrieve all colums from a table
    so trying to select only a part of the columns will throw an error.
  2. Unless you’re using Oracle PL/SQL, MS SQL Server or something, SQL doesn’t support SELECT CASE statements directly in SQL
  3. I think that you wanna convert integers into currency format.
    In this case it isn’t done with a CASE or WHEN function anyways.

So what kind of SQL are you using?

Your SQL looks pretty good :slight_smile: I suspect you might be getting an error because of some extra quotation marks.

When I’ve worked with SQL, I’ve never needed to put quotes around the names of tables, columns, or aliases. Try removing those quotes and see if your query works.

For example, instead of this:

END AS ‘Bucketed_Net_Revenue’

Try this:

END AS Bucketed_Net_Revenue

And so on.

1 Like

After many iterations and making the query a little more complicated, I got it to work. The null values within the table were throwing a lot of errors so had to do a sub-select which I did not know you could do. Also, found out that I needed to use PostgreSQL for this specific application.

SELECT *, (OrderTotalAmt - ShippingAmt - DiscountAmt) AS Net_Revenue,
CASE
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) BETWEEN 0 AND 9.99 THEN ‘$0-$9.99’
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) BETWEEN 10 AND 19.99 THEN ‘$10-$19.99’
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) BETWEEN 20 AND 29.99 THEN ‘$20-$29.99’
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) BETWEEN 30 AND 39.99 THEN ‘$30-$39.99’
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) BETWEEN 40 AND 49.99 THEN ‘$40-$49.99’
WHEN (OrderTotalAmt - ShippingAmt - DiscountAmt) >= 50 THEN ‘$50+’
END AS Bucketed_Net_Revenue
FROM
(SELECT order_id, customer_id,
CASE
WHEN order_total is null THEN 0.00
WHEN order_total is not null THEN order_total
END as OrderTotalAmt,
CASE
WHEN shipping is null THEN 0.00
WHEN shipping is not null THEN shipping
END as ShippingAmt,
CASE
WHEN discount is null THEN 0.00
WHEN discount is not null THEN discount
END as DiscountAmt
FROM ““Orders””) AS OrdersCalculated;

1 Like