Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any other logical or more robust way

I have a sql query

select count(salary)
from USER_DETAILS 
where salary>0 and salary<1000 union all
select count(salary)
from USER_DETAILS 
where salary>1000 and salary<10000 union all
select count(salary)
from USER_DETAILS 
where salary>10000 and salary<100000

Is there any other logical or more robust way of executing the same query(in hql preferably).

Thanks in advance

like image 401
CodeHunter Avatar asked Jan 15 '23 04:01

CodeHunter


2 Answers

give this a try,

SELECT  COUNT(CASE WHEN salary >= 0 and salary <= 1000 THEN salary END) "salary >= 0 and salary <= 1000",
        COUNT(CASE WHEN salary >= 1000 and salary <= 10000 THEN salary END) "salary >= 1000 and salary <= 10000",
        COUNT(CASE WHEN salary >= 10000 and salary <= 100000 THEN salary END) "salary >= 10000 and salary <= 100000"
from    user_details
like image 94
John Woo Avatar answered Jan 17 '23 04:01

John Woo


Try it using the CASE Statement

select  
     count(case when sal between 0 and 1000 then 1 end)
     count(case when sal between 1000 and 10000 then 1 end) ,
     count(case when sal between 10000 and 100000 then 1 end)
from user_details;

Note: You also have else keyword which can be placed at the end (if needed)

For example Refer
1. here
2. This one goes with your requirement

like image 40
asifsid88 Avatar answered Jan 17 '23 04:01

asifsid88