Help with SQL Query Count

Question

I have a table similar to the following

ID NUMBER
VAL1 NUMBER
VAL2 NUMBER

I need an SQL query that displays count of rows that have VAL1 > VAL2 and Count of rows that have VAL1 < VAL2. Both counts using one SQL query. Thanks.

Answer 1

This query should work with most database platforms:

 select sum(case when val1 > val2 then 1 end) as GreaterThanCount,
    sum(case when val1 < val2 then 1 end) as LessThanCount
from MyTable

To show the sums in separate rows, you can do:

select case when val1 > val2 then 'GreaterThan' else 'LessThan' end as Type,
    count(*) as Count
from MyTable    
group by case when val1 > val2 then 'GreaterThan' else 'LessThan' end

Answer 2

SELECT SUM(VAL1 > VAL2) as arg1, SUM(VAL1 < VAL2) as arg2

Answer 3

Try this:

select id, count(VAL1 > VAL2) as more1, count(val2 > val1) as more2
from your_table