CODEWITHSUNDEEP
mysqlsqlsybase
riderchap
riderchap

Reputation: 687

SQL Query - Multiple counts on multiple conditions

Say there is a simple table named "Table1" with just one column "responseTime" of integer type. I am looking to get the count of rows with value of "responseTime" less than 10, count of rows less than 50 and count of rows less than 100, in a single query.

I need to run this in Sybase and MySql.

Upvotes: 0

Views: 306

Answers (3)

matri70boss
matri70boss

Reputation: 347

You could do something like

SELECT 
 SUM(IF(responseTime<10,1,0)) as data1,
 SUM(IF(responseTime<=10 AND responseTime<50,1,0)) as data 2,
 SUM(IF(responseTime<100,1,0)) as data3
FROM table1
WHERE ...

Upvotes: 0

Zak
Zak

Reputation: 7515

IF OP wanted 3 seperated rows rather than fields, each containing the count, one could just use UNION 

SELECT count(*) WHERE responseTime < 10
UNION
SELECT count(*) WHERE responseTime <= 10 AND responseTime < 50
UNION
SELECT count(*) WHERE responseTime >= 50 AND responseTime < 100

This will return 3 separated rows with the respective count in each row.

It is unclear if OP wants a separated result set or a single result

Upvotes: 0

Alex
Alex

Reputation: 17289

SELECT 
    SUM(responseTime < 10),
    SUM(responseTime >= 10 AND responseTime < 50),
    SUM(responseTime >= 50 AND responseTime < 100)
FROM Table1

After @L.Scott Johnson comment, I am not sure what is your goal. So here is another approach if you need double counted records:

SELECT 
    SUM(responseTime < 10),
    SUM(responseTime < 50),
    SUM(responseTime < 100)
FROM Table1

Upvotes: 2

Related Questions