Select query to retrieve rows with null values

Question

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?

Answer 1

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.

Answer 2

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.

• Philᵀᴹ: Oracle has NVL()
• Carlos: Very sorry, i use Postgresql 9. Thank you.
• Erwin Brandstetter: 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.

Answer 3

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