Home » SQL & PL/SQL » SQL & PL/SQL » group_by conditions
group_by conditions [message #259811] Thu, 16 August 2007 10:30 Go to next message
durai
Messages: 38
Registered: December 2006
Member
vanakkam to all,

is it possible to make the below query to a single query
i tried CASE statement to make it into a single query
but it throws a error as emp.empno is not a single-group group function

SELECT COUNT(emp.empno) FROM sal,emp WHERE gam.empno = emp.empno AND sal.esal< 25000 AND del_flg !='Y'

SELECT COUNT(emp.empno) FROM sal,emp WHERE gam.empno = emp.empno AND sal.esal >=25000 AND sal.esal < 50000 AND del_flg !='Y'

SELECT COUNT(emp.empno) FROM sal,emp WHERE gam.empno = emp.empno AND sal.esal >=50000 AND sal.esal < 100000 AND del_flg !='Y'

SELECT COUNT(emp.empno) FROM sal,emp WHERE gam.empno = emp.empno AND sal.esal >=100000 AND sal.esal < 500000 AND del_flg !='Y'

SELECT COUNT(emp.empno) FROM sal,emp WHERE gam.empno = emp.empno AND sal.esal >= 200000 AND del_flg !='Y'


so i tried using sub query concept(it works) but what i want to know is ,is it possible to make it in a single query or any other way since i heard that using sub-query may lead to poor Performance is it so
SELECT COUNT(ind) FROM
(
SELECT
CASE WHEN sal.esal < 25000 THEN 'A'
WHEN sal.esal >=25000 AND sal.esal < 50000 THEN 'B'
WHEN sal.esal >=50000 AND sal.esal < 100000 THEN 'C'
WHEN sal.esal >=100000 AND sal.esal < 500000 THEN 'D'
WHEN sal.esal >= 500000 THEN 'D'
END ind
FROM sal,emp WHERE gam.empno = emp.empno AND del_flg !='Y'
) GROUP BY ind

THANX IN ADVANCE
Re: group_by conditions [message #259812 is a reply to message #259811] Thu, 16 August 2007 10:38 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I doubt there would be much difference in performace, but you would use:

SELECT 
count(CASE WHEN sal.esal < 25000 THEN 1 END) as count1,
count(case WHEN sal.esal >=25000 AND sal.esal < 50000 THEN 1 END) as count2,

...
FROM sal,emp WHERE gam.empno = emp.empno AND del_flg !='Y'

[Updated on: Thu, 16 August 2007 10:41]

Report message to a moderator

Previous Topic: Help on rank() function
Next Topic: ORA-01002: fetch out of sequence
Goto Forum:
  


Current Time: Sun Dec 04 20:55:06 CST 2016

Total time taken to generate the page: 0.05609 seconds