r/SQL • u/Formal_Ad_626 • 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



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/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.
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