SQL & PL/SQL

Why not just..

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select count(empno) as emps
  2       , count(mgr) as mgrs
  3       , sum(case when sal > 2000 then 1 else 0 end) as sals
  4* from emp
SQL> /

      EMPS       MGRS       SALS
---------- ---------- ----------
        14         13          6

SQL>

TRy this query.

REgards Salim.

SELECT COUNT (empno) empno, COUNT (mgr) mgr,
       COUNT (CASE
                 WHEN sal > 2000
                    THEN 1
              END) sal
  FROM emp;

.. although your requirement seems a little odd.

Are you perhaps looking to count distinct values?

SQL> ed
Wrote file afiedt.buf

  1  select count(distinct empno) as emps
  2       , count(distinct mgr) as mgrs
  3       , count(distinct case when sal > 2000 then sal else null end) as sals
  4* from emp
SQL> /

      EMPS       MGRS       SALS
---------- ---------- ----------
        14          6          5

SQL>

Hi,

There's no need to use daul: all the values you need are in emp:

SELECT	COUNT (empno)		AS empno_cnt
,	COUNT (mgr)		AS mgr_cnt
,	COUNT ( CASE
		    WHEN  sal > 2000
		    THEN  sal
		    ELSE  NULL		-- This is the default, so you don't really need this line
		END
	      )			AS sal_2000_cnt
FROM	emp;

The tricky part is counting all emps and mgrs, but counting only some sals.
A WHERE clause applies to the whole query, so we don't want that.
A CASE statement can use the same kinds of conditions as a WHERE clause, but they only apply to one column, which is exactly what we want here.

hope this is usefull..............

select empno,mgr,
(select count(sal) from emp  where sal>2000)  as sal
from(
select count(empno) as empno,count(mgr) as mgr from emp )

Edited by: user10734616 on Apr 8, 2009 6:02 AM

Thanks for your immediate Reply...
I have been converting procedure SQL SERVER to Oracle..
They used temporary table .. But i want to avoid TEMP table

set @invent=(select count(inventoryReport)as invent from AdDashRoleProfile where inventoryReport != 1 and roleid=@roleid)    
set @development=(select count(development)as develop from AdDashRoleProfile where development != 1 and roleid=@roleid)    
set @Execution=(select count(Execution)as Exe from AdDashRoleProfile where Execution != 1 and roleid=@roleid)    
insert into @tempRole(inventoryReport,development,Execution) values(@invent,@development,@Execution)    
select * from @tempRole 

So I changed my query...

SELECT (SELECT COUNT (inventoryreport)
          FROM addashroleprofile
         WHERE inventoryreport  != 1 AND roleid = v_roleid) inventoryreport,
       (SELECT COUNT (development)
          FROM addashroleprofile
         WHERE development  != 1 AND roleid = v_roleid) development,
       (SELECT COUNT (execution)
          FROM addashroleprofile
         WHERE execution  != 1 AND roleid = v_roleid) execution
  FROM DUAL;

Your solutions are good..
How can I apply it..???

TRy this code.

REgards salim.

/* Formatted on 2009/04/08 09:22 (Formatter Plus v4.8.8) */
SELECT COUNT
            (CASE
                WHEN inventoryreport != 1 AND roleid = v_roleid
                   THEN inventoryreport
             END
            ) inventoryreport,
       COUNT (CASE
                 WHEN development != 1 AND roleid = v_roleid
                    THEN development
              END
             ) development,
       COUNT (CASE
                 WHEN execution != 1 AND roleid = v_roleid
                    THEN execution
              END
             ) execution
  FROM addashroleprofile;

Edited by: Salim Chelabi on 2009-04-08 06:20

Thank you... It works.. :):)

Blu:

Just curious. Why not:

SELECT COUNT(CASE WHEN inventoryreport != 1 THEN inventoryreport END) inventoryreport,
       COUNT(CASE WHEN development != 1 THEN development END) development,
      COUNT(CASE WHEN execution != 1 THEN execution END) execution
FROM addashroleprofile
WHERE roleid = v_roleid; 

John

Hi John,

Yeah, good remark.

REgards salim.

Yeah, did spot that myself after, but was a little busy to come back and edit. ;)

I arranged yours B-)
We can use NullIF

select count(nullIF(inventoryreport,1)) inventoryreport
       count(nullIF(development,1)) development
       count(nullIF(execution,1)) execution
  from addashroleprofile
 WHERE roleid = v_roleid;