1

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.

Community
  • 1
  • 1
SvbZ3r0
  • 638
  • 4
  • 19

1 Answers1

1

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
Alexey Romanov
  • 167,066
  • 35
  • 309
  • 487
  • I'm sorry, I'll reword the question, but you assumed right and your answer is exactly what I was looking for. I haven't come across the `case` keyword before, but like I said, I'm new to sql and have much to learn. Thank you for your time – SvbZ3r0 Jun 14 '16 at 07:34
  • I'm not a native english speaker and can't see a way to word it better. Can you reword the question for me? Thanks again – SvbZ3r0 Jun 14 '16 at 07:36