How to find out why a SQL query does not produce rows as expected?

Question

I am very new to programming. In my database class, I have to "return only invoices that have a balance due that's greater than 50". When I run this, nothing shows up. What am I doing wrong?

USE ap;
SELECT invoice_number, payment_total + credit_total AS 'payment_credit_total' , invoice_total - 'payment_credit_total' AS 'balance_due'
FROM invoices
WHERE 'balance_due' > 50
ORDER BY 'balance_due' DESC

Luckily, readers have spotted the problem with this query. However, normally we ask that question askers prepare the smallest possible test dataset that highlights the problem. As it stands, if someone does not spot a query problem, the answer could be that the table in question is empty. Consider adding a SQL Fiddle to your questions - this can contain a simple dataset too. — halfer, Feb 28 '17 at 20:17

score -1 · Accepted Answer · answered Feb 27 '17 at 14:24

-1

balance_due is an alias for your expression, it's just a label on your result set. However, you must not use it within your query.

SELECT invoice_number, payment_total + credit_total AS 'payment_credit_total' , invoice_total - payment_total + credit_total AS 'balance_due'
FROM invoices
WHERE invoice_total - payment_total + credit_total > 50
ORDER BY invoice_total - payment_total + credit_total DESC

answered Feb 27 '17 at 14:24

Psi

6,387
3
16
26

Not quite. You *cannot* use it in the WHERE clause, but you *can* use it in the ORDER BY clause. – Strawberry Feb 27 '17 at 14:35
True, in the order clause you can even set numeric indexes – Psi Feb 27 '17 at 14:37

How to find out why a SQL query does not produce rows as expected?

1 Answers1