r/SQL • • 1d ago

PostgreSQL Where did I go wrong?

For context, photos 1-2 show the original prompt + my answer. Photo 3 shows the solution. The output is somewhat similar to the correct answer, but not quite the same.

When I move the "status = 'Completed'" either to the "WHERE" section or to the JOIN section as "AND status = 'Completed'" it solves the problem, but not sure exactly why.

16 Upvotes

7 comments sorted by

4

u/Aggressive_Ad_5454 1d ago

COUNT doesn’t work that way. It treats the computed Boolean expressions never as NULL. And COUNT(thing) counts all the non-null things.

You could do what you want with this, as it adds up the matching rows one at a time.

SUM(CASE WHEN Status=‘Completed’ THEN 1 ELSE 0 END)

But it’s hilariously verbose. Go with your WHERE clause instead.

2

u/Formal_Ad_626 22h ago

Hm I guess I forget sometimes how rigid certain rules are, because it's a very structured language. I suppose I should keep any filtering parameters in the WHERE section.

2

u/Anonononomomom 23h ago

As others have stated but to help you understand [ status = ‘completed’ ] will return a Boolean function of either true/false or as the compiler will read as 1 or 0 - whether you count(0) or count(1) you are still counting both of these occurrences. Thus you need to either do a case when within the count function or filter the query.

1

u/Formal_Ad_626 22h ago

Ahhhhh yes I wasn't familiar with the term, thanks for clarifying!

8

u/reditandfirgetit 1d ago

Status=value evaluates a boolean, there is always a value. So your count , counts every record for that city.

Using the where clause limits the results to filter just what you want to include in your results.

1

u/Formal_Ad_626 22h ago

Thanks everyone for explaining! That makes SO much sense! Omg learning is so fun, absolutely obsessed with the word "Boolean."

2

u/cobaltsignal 23h ago

select
u.city
, sum( case when t.status = 'Completed' then 1 else 0 end ) city_order_sum
from trades t
join users as u on t.user_id = u.user_id
group by
u.city
order by city_order_sum desc
limit 3