0

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?

2 Answers 2

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
1
  • Brilliant @alex07!! Thanks!!! Looks like I missed a "count" and also misplaced parenthesis.
    – tkmagnet
    Sep 14, 2023 at 14:35
0

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;
2
  • 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?
    – tkmagnet
    Sep 13, 2023 at 17:45
  • if you edit your post to include tables schema and sample data, i might be able to provide a better answer
    – Bob Klimes
    Sep 13, 2023 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.