Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some help in a query. Need to retrieve data from 1 table, even if one of the field 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 only "name" and the "SUM(credit)", when the "credit" has values. I need to retrieve all from name even if "credit" has no value at all.

Is this possible?

share|improve this question
    
Which RDBMS are you using? –  FreshPhilOfSO Jan 14 at 1:04
    
Postgresql 9. Thx –  Carlos Jan 14 at 12:14
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Oracle has NVL() –  FreshPhilOfSO Jan 14 at 1:04
    
Very sorry, i use Postgresql 9. Thank you. –  Carlos Jan 14 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. –  Erwin Brandstetter Jan 14 at 19:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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