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.