Home » SQL & PL/SQL » SQL & PL/SQL » Multiple counts as one (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Multiple counts as one [message #611900] Wed, 09 April 2014 22:22 Go to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
I have a table called JOBDETL - I need to find the total no of jobs in a month, jobs by age ( ie age <=25 and age >25). If the same pers_id has multiple jobs for a month, count it as one.

I was able to write the query and get the desired result but was using DISTINCT to get this. I would like to know if there is any other to do this without using DISTINCT.

CREATE TABLE JOBDETL
  (
    ID             NUMBER,
    PGM_WID        NUMBER,
    PGMSTAT_WID    NUMBER,
    CASE_WID       NUMBER,
    REDETER_DT_WID NUMBER,
    PERS_WID       NUMBER,
    PART_AGE       NUMBER,
    LANG_WID       NUMBER,
    EMP_WID        NUMBER,
    HIREDT_WID     NUMBER,
    EMPCAT_WID     NUMBER,
    JOBTYPE_WID    NUMBER,
    EMPSTAT_WID    NUMBER,
    WAGE           NUMBER,
    JOB_HRS        NUMBER,
    POS_WID        NUMBER
  )


REM INSERTING into JOBDETL
SET DEFINE OFF;
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (103,10,5,852,3558,44,-1,3,35,20091027,8.15,-1,14,7,0,129);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (104,10,5,855,2980,40,-1,3,181,20091028,23,-1,16,7,0,522);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (107,10,5,85,2844,43,-1,-1,28,20091022,0,-1,15,6,0,316);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (115,10,5,85,1842,39,-1,3,110,20091022,0.99,-1,9,6,0,316);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (116,10,5,85,1842,39,-1,3,43,20091022,0,-1,16,6,0,316);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (122,10,5,855,1083,40,-1,3,48,20091028,0,-1,11,6,0,522);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (149,10,7,719,2545,20,20100930,3,71,20091024,0,-1,16,6,10,419);
Insert into JOBDETL (ID,PGM_WID,PGMSTAT_WID,CASE_WID,PERS_WID,PART_AGE,REDETER_DT_WID,LANG_WID,EMP_WID,HIREDT_WID,WAGE,
EMPCAT_WID,JOBTYPE_WID,EMPSTAT_WID,JOB_HRS,POS_WID) values (150,10,7,719,2545,20,20100930,3,138,20091029,0,-1,11,-1,9,419);


Select statement:

SELECT COUNT(1),
  TO_DATE(hiredt,'MM-RRRR') hiredt,
  COUNT(1) cnt,
  COUNT(less25),
  COUNT(above25)
FROM
  ( SELECT DISTINCT pers_Wid,
    TO_CHAR(TO_DATE(hiredt_wid,'RRRR-MM-DD'),'MM-RRRR')hiredt,
    CASE
      WHEN part_age <=25
      THEN 1
    END less25 ,
    CASE
      WHEN part_age >25
      THEN 1
    END above25
  FROM JOBDETL
  WHERE TO_CHAR(TO_DATE(hiredt_wid,'RRRR-MM-DD'),'MM-RRRR')='10-2009'
  )
GROUP BY hiredt
ORDER BY 2 ;

[Updated on: Thu, 10 April 2014 01:02] by Moderator

Report message to a moderator

Re: Multiple counts as one [message #611909 is a reply to message #611900] Thu, 10 April 2014 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select trunc(hiredt_wid/100) "Month",
  2         count(distinct pers_wid) "Total",
  3         count(distinct case when part_age <= 25 then pers_wid end) "<= 25",
  4         count(distinct case when part_age > 25 then pers_wid end) "> 25"
  5  from JOBDETL
  6  group by trunc(hiredt_wid/100)
  7  order by 1
  8  /
     Month      Total      <= 25       > 25
---------- ---------- ---------- ----------
    200910          6          1          5


Remark: Storing date into a NUMBER column is a very bad design and implementation. What prevent from inserting -1, 0 or 20149999?

Re: Multiple counts as one [message #612102 is a reply to message #611909] Sat, 12 April 2014 01:25 Go to previous messageGo to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
Thanks for your reply. Storing date as a number is a warehouse thing.
Re: Multiple counts as one [message #612122 is a reply to message #612102] Sat, 12 April 2014 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 22691
Registered: January 2009
Senior Member
>Storing date as a number is a warehouse thing.
storing DATE as a NUMBER is a amateurish, foolish, problematic & wrong thing!
Re: Multiple counts as one [message #612135 is a reply to message #612122] Sat, 12 April 2014 12:27 Go to previous message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
Agreed - that was not my design but will add another DATE column and store it as date than number. Thanks.
Previous Topic: Outer Join and MAX
Next Topic: ORA-01830: date format picture ends before converting entire input string
Goto Forum:
  


Current Time: Sat Aug 23 01:26:21 CDT 2014

Total time taken to generate the page: 0.14576 seconds