SvbZ3r0 SvbZ3r0 - 5 months ago 14
SQL Question

If exists ... else clause in SQLite

I have a db and need to extract some data from it for use in a python script. I'm new to sql and just know the basics.

This is what I want to do:

if exists (
select date, sum(amount)
from transactions
where category = 'Food'
group by date
)
else
select date, 0


For all dates present in
transaction
table, I want to get date, sum(amount) for category Food. If there are no transactions with category Food I want to get date, 0 (since sum(amount) of Food that day is 0).

The code I currently have is

select date, sum(amount)
from transactions
where category = 'Food'
group by date


but it doesn't contain the date, 0 rows for dates which don't have any Food transactions.

How should I rewrite this to make it work?

I have read this question, but the answer there is exclusive to boolean data, doesn't provide enough details and can't be generalized.

Answer

Your question doesn't make it clear for which dates you want to get 0: you can't just list all dates which aren't present in your data, there is an infinite number of them! However, I guess that you meant to get date, 0 for those dates where there are transactions, but none of them have category 'Food'. In this case you can do this:

select date, sum(case when category = 'Food' then amount else 0 end)
from transactions
group by date
Comments