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

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;