Comparing 2 count columns with case

Question

I have 2 tables, size and cost. My query does a count with a greater than comparison of cost and counts different sizes.

table1: size u
table2: cred t

Here is my query:
select u.size as size
,count(u.size) * .4 as "40% of size"
,count(case when (t.cred * 12) > (t.inc *.3) then 1 end) as "30% cost"
from cred t
join size u on t.id = u.id
group by u.size

The result is as follows:

Size	40% of size	30% cost
0	75.6	50
1	470.4	160
2	414.8	114
3	202.8	59
4	40	21
5	8.8	9
6	0.4	1

I want the query to show a 'Y' if the cost column is larger than the size column and a 'N' if it is smaller. Here would be the result I'm looking for:

Size	40% of size	30% cost	Y/N
0	75.6	50	N
1	470.4	160	N
2	414.8	114	N
3	202.8	59	N
4	40	21	N
5	8.8	9	Y
6	0.4	1	Y

Is there a way to show this? I tried to use the following, but I get this: Boolean value cannot be operated with non-Boolean value.

,case when ((t.cred * 12) > (t.inc * .3)) > (count(u.size) * .4) then 'Y' else 'N' end as "Y/N"
Not sure if I need to do a subquery?

Answer 1

You can use subquery or cte to make it more readable, but you don't have to. Version with subquery :

select a.* , 
case 
 when [30% cost]>[40% of size] then 'Y' 
 else 'N' 
end as [Y/N]
from 
( 
 select u.size as size
 ,count(u.size) * .4 as "40% of size"
 ,count(case when (t.cred * 12) > (t.inc *.3) then 1 end) as "30% cost"
 from cred t
 join size u on t.id = u.id
 group by u.size
)a

Without subquery

 select u.size as size
 ,count(u.size) * .4 as "40% of size"
 ,count(case when (t.cred * 12) > (t.inc *.3) then 1 end) as "30% cost"
 case 
 when count(case when (t.cred * 12) > (t.inc *.3) then 1 end) > count(u.size) * .4 then 'Y'
 else 'N' 
 end as [Y/N]
 from cred t
 join size u on t.id = u.id
 group by u.size

• tkmagnet: Brilliant @alex07!! Thanks!!! Looks like I missed a "count" and also misplaced parenthesis.

Answer 2

you could probably use a CTE to get your desired result.

something like this:

with cte as(
select u.size as size
,count(u.size) * .4 as "40% of size"
,count(case when (t.cred * 12) > (t.inc *.3) then 1 end) as "30% cost"
from cred t
join size u on t.id = u.id
group by u.size )
select Size,[40% of size],[30% cost],
case when [30% cost]>[40% of size] then 'Y'
 Else 'N' as [Y/N]
from cte;

• tkmagnet: I tried to use a cte, but the error keeps coming back: Your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET, DEBUG or a script statement. I'm assuming that I would need to use a subquery?
• Bob Klimes: if you edit your post to include tables schema and sample data, i might be able to provide a better answer