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.

D'Arcy Rittich · Accepted Answer · 2011-05-31 18:32:08Z

4

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

edited May 31, 2011 at 18:32

answered May 31, 2011 at 17:29

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M99 Over a year ago

Is it possible to display the counts in two different rows with the same SQL query?

slothrop Over a year ago

Note that rows where val1 = val2 are caught in the "LessThan" bucket here.

mrkmg · Accepted Answer · 2011-05-31 17:30:31Z

1

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

answered May 31, 2011 at 17:30

mrkmg

2414 silver badges14 bronze badges

1 Comment

M99 Over a year ago

Is it possible to display the counts in two different rows with the same SQL query?

duffymo · Accepted Answer · 2011-05-31 17:30:00Z

0

Try this:

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

answered May 31, 2011 at 17:30

duffymo

310k46 gold badges376 silver badges571 bronze badges

1 Comment

M99 Over a year ago

Is it possible to display the counts in two different rows with the same SQL query?

Collectives™ on Stack Overflow

Help with SQL Query Count

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related