SQL Syntax error in case statement

Question

I have 2 database tables. One that tracks placements of orders made to a company, and the other that tracks payments made to the company for orders.

I'd like to create an SQL query, that displays each order ID, the total amount for the order, and the amount outstanding for the order, which is yet to be paid. Customers are allowed to pay in 'instalments' say, So the purpose is to calculate the total outstanding and display it.

here is what i have so far, however it is returning 'null' when no payments have been made. This obviously means that there are no records based on a specific order, and so the total amount paid by the customer = 0. Therefore the amount outstanding is equal to the total amount.

I would like my sql code to display the total amount of the order when the entire amount is still outstanding, instead of the null.

How can this be done?

I tried a case statement but I think my syntax is wrong.

Here's the version without the case statement, which returns null when no payments have been made:

 SELECT
     pb_orders.id AS 'Order ID', pb_orders.order_total AS 'Total',
     SUM(pb_payments.amount) AS 'Amount Paid', pb_orders.order_total -
     SUM(pb_payments.amount) AS 'Outstanding Amount'
 FROM 
     pb_orders 
 INNER JOIN 
     pb_payments ON pb_orders.id = pb_payments.link_id 
 WHERE 
     pb_orders.id = 1556

And here is my attempt at the case statement:

SELECT
    pb_orders.id AS 'Order ID', pb_orders.order_total AS 'Total',
    SUM(pb_payments.amount) AS 'Amount Paid' 
        WHEN 'Amount Paid' = 'NULL' 
           THEN 'Amount Paid' = pb_orders.order_total, 
    pb_orders.order_total - SUM(pb_payments.amount) AS 'Outstanding Amount',  
FROM 
    pb_orders 
INNER JOIN 
    pb_payments ON pb_orders.id = pb_payments.link_id 
WHERE 
    pb_orders.id = 1555

I removed the extraneous database tags. Feel free to add the tag for the database you are actually using. — Gordon Linoff
– Gordon Linoff, Commented Jan 27, 2016 at 20:27
strange, you even say that you are using a CASE, but I don't see CASE anywhere — Lamak
– Lamak, Commented Jan 27, 2016 at 20:29
Looks like part of the syntax for a case statement is there (e.g. WHEN and THEN but you haven't included CASE and END) — Bridge
– Bridge, Commented Jan 28, 2016 at 11:34

Gordon Linoff · Accepted Answer · 2016-01-28 03:18:57Z

3

You need coalesce() to turn NULL values into something else:

SELECT o.id AS OrderID, o.order_total AS Total,
       sum(p.amount) AS AmountPaid,
       (o.order_total - coalesce(sum(p.amount), 0) ) AS OutstandingAmount
FROM pb_orders o INNER JOIN
     pb_payments p
     ON o.id = p.link_id 
WHERE o.id = 1556;

Also note that table aliases make the query easier to write and to read.

I recommend that you never use single quotes to define column aliases. This is a bad habit that leads to trouble -- one day, you will refer to a column in single quotes, but the column will be interpreted as a string constant instead.

edited Jan 28, 2016 at 3:18

answered Jan 27, 2016 at 20:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marko Vidalis Over a year ago

Thank you very much for your help! Unfortunately I never set up the database, I'm working with something that was given to me.

Marko Vidalis Over a year ago

Unfortunately I'm getting an error with that code! I replaced all the table aliases to the correct ones.

Marko Vidalis Over a year ago

I think I have to make use of the ISNULL statement instead of the Coalesce one, I'm just not sure of how the syntax works. I'm trying to work it out though

Uueerdo Over a year ago

Answer is missing a ) before the AS, IFNULL would be my preference as well, but the ) would still be needed, or the ( removed from before o.order_total

Scottley · Accepted Answer · 2016-01-27 21:10:55Z

using ISNULL:

 SELECT
     pb_orders.id AS 'Order ID', 
     pb_orders.order_total AS 'Total',
     SUM(ISNULL(pb_payments.amount, 0)) AS 'Amount Paid', 
     pb_orders.order_total - SUM(ISNULL(pb_payments.amount, 0)) AS 'Outstanding Amount'
 FROM 
     pb_orders 
 INNER JOIN 
     pb_payments ON pb_orders.id = pb_payments.link_id 
 WHERE 
     pb_orders.id = 1556

Using a Common Table Expression to pre-process the possible null amount:

    DECLARE @orderId INT = 1556
    ;WITH [SummedOrderAmount] AS (
        SELECT
          SUM(ISNULL([amount], 0)) AS [AmountPaid]
        FROM
          pb_payments 
        WHERE
          pb_payments.link_id = @orderId
     )
      SELECT
         pb_orders.id AS 'Order ID', pb_orders.order_total AS 'Total',
         s.[AmountPaid] AS 'Amount Paid', 
         pb_orders.order_total - s.[AmountPaid] AS 'Outstanding Amount'
     FROM 
-- just use a cartesian join because you are looking at a single record.
         pb_orders, 
         [SummedOrderAmount] s
     WHERE 
         pb_orders.id = @orderId

Collectives™ on Stack Overflow

SQL Syntax error in case statement

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related