1

I need to retrieve data from a table even if one of the fields has null value. Here's an example:

Select name, SUM(credit) as credit
From expenses
Where name like 'vendor0%'
and date like '2013%'
Group by name
Order by name asc

This example retrieves name and SUM(credit) only when credit has values. I need to retrieve all from name even if credit has no value at all.

Is this possible?

0

3 Answers 3

7

This example retrieves only "name" and the "SUM(credit)", when the "credit" has values.

The query you presented will retrieve a row for every present name, even if all associated credit columns are NULL. You get a row with a NULL value for SUM(credit) then. Null values are just ignored by the aggregate function sum():

You only get no row for a particular name if no row for that name exists in the table expenses for the given WHERE expressions.

I am assuming you want
.. only names matching 'vendor0%'
.. but all of those, even if they have no expenses in 2013.

Your query could work like this:

SELECT name, SUM(CASE WHEN date LIKE '2013%' THEN credit END) AS credit
FROM   expenses
WHERE  name LIKE 'vendor0%'
GROUP  BY name
ORDER  BY name

CASE defaults to NULL if no ELSE branch is given.
Aside: You shouldn't store date / time values as text. Use an appropriate type, it has many advantages.
And don't use "name" or "date" as identifiers. "name" is not a descriptive name and "date" is a reserved word in standard SQL and a function and base type name in Postgres.

1

Replace SUM(credit) with SUM(COALESCE(credit,0)) or SUM(ISNULL(credit,0)).

You don't say what database you are using, so you may not have both the above functions available. If you have both, see your documentation or run benchmarks to see which (if either) performs better.

3
  • Oracle has NVL()
    – Philᵀᴹ
    Jan 14, 2014 at 1:04
  • Very sorry, i use Postgresql 9. Thank you.
    – Carlos
    Jan 14, 2014 at 11:57
  • sum() ignores NULL values anyway. The only effect: you get 0 instead of NULL for groups with only NULL values. But that could be had cheaper with COALESCE(sum(...), 0) and doesn't seem to be the problem to begin with. Rows seem to be missing, and COALESCE() won't help with that. Jan 14, 2014 at 19:45
0

You can try this one for solve your problem,

Select name
From expenses
Where name like 'vendor0%'
and date like '2013%'
and credit IS NULL
Group by name
Order by name asc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.