Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    2

    Unanswered: Multiple use of count in same query

    Hi,

    I'm trying to write a query that will give me results that look like this:

    widgit model, Prod_type, Step, count(within 24hours), count(1week) ,Count(2Weeks),Count(3weeks), etc

    I'm using Oracle 8. I've tried a few things but I don't see a way to do mutliple condiditional counts. Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    That might give you a clue:
    -------------------------------

    SQL> select * from demo;

    ID DEMO_TEXT DEMO_DATE
    ---------- ------------------------------ ---------
    1 TEST 30-APR-02
    2 TEST 2 29-APR-02
    3 TEST 3 28-APR-02
    4 TEST 4 23-APR-02
    4 MONTH 31-MAR-02
    4 YEAR 30-APR-01
    5 YEAR2 30-APR-01
    6 YEAR3 30-APR-01
    7 YEAR4 30-APR-01
    8 MONTH 31-MAR-02
    9 MONTH 31-MAR-02

    ID DEMO_TEXT DEMO_DATE
    ---------- ------------------------------ ---------
    10 WEEK2 23-APR-02
    11 MONTH 30-APR-01
    12 MONTH 30-APR-01
    13 MONTH 23-APR-02

    15 rows selected.


    Then use:
    -----------

    select DISTINCT(a.demo_text),
    (select count(demo_date) from demo where demo_date like sysdate - 7 and demo_text = 'MONTH') "WEEK",
    (select count(demo_date) from demo where demo_date like sysdate - 30 and demo_text = 'MONTH') "MONTH",
    (select count(demo_date) from demo where demo_date like sysdate - 365 and demo_text = 'MONTH') "YEAR"
    from demo a
    where demo_text = 'MONTH';


    You will get that:
    -------------------

    DEMO_TEXT WEEK MONTH YEAR
    ------------------------------ ---------- ---------- ----------
    MONTH 1 3 2


    Check the latest Oracle DBA forums at:
    http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi


    Hope that helps,



    Trifon Anguelov
    Senior Oracle DBA

  3. #3
    Join Date
    Apr 2002
    Posts
    2
    Thanks, this is real close to what I need. What if I wanted to see the different demo_texts. Here is my code with all of the models:

    select model_number,
    design_level,
    prod_type,
    last_prod_step,
    count(model_number) as total
    from ecu_production_control
    where last_prod_step between 51 and 59
    and disposition <> 'S'
    and model_number not in
    (select distinct model_number
    from ecu_model_definition
    where srs_check = 1)
    group by model_number, design_level, prod_type, last_prod_step

    and it gives results like this:
    MODE DESI P LAST_PROD_STEP TOTAL
    ---- ---- - -------------- - --------
    1197 X502 M 59 1
    1683 Y531 M 59 4

    I would like to break down this TOTAL field into within 24 hours, with 1 week, 2 weeks and over 2 weeks. I've tried messing around with the code you posted but my database is huge and takes forever to see if it works.
    Here is what the table looks like:
    Name Null? Type
    ------------------------------- -------- ----
    PCB_SERIAL_NUMBER
    MODEL_NUMBER
    MODEL_YEAR
    LABEL_SERIAL_NUMBER
    LAST_PROD_STEP
    PROD_TYPE
    DISPOSITION
    DESIGN_LEVEL
    LAST_PROD_DATE_TIME


    Your help is greatly appriciated. Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    I assume, that you want to count the intervals by LAST_PROD_DATE_TIME? Can you create separate query for each interval by adding a where clause?

    ...
    ...
    where LAST_PROD_DATE_TIME between SYSDATE-1 and SYSDATE (for 24 hours), etc. Then create separate views with those queries and then
    SELECT from them.

    If you want to get the results in one query, then you can create function for each count.
    Create a cursor with one query, pass as IN variables (where last_prod_step between 51 and 59
    and disposition <> 'S'
    and model_number not in
    (select distinct model_number
    from ecu_model_definition
    where srs_check = 1) all of those, fetch the count, and return it as variable. So after you create function for each count, then run:

    select model_number,
    design_level,
    prod_type,
    last_prod_step,
    function1 as 24_HOURS,
    function2 as ONE_DAY,
    ...
    ...
    from ecu_production_control
    where last_prod_step between 51 and 59
    and disposition <> 'S'
    and model_number not in
    (select distinct model_number
    from ecu_model_definition
    where srs_check = 1)
    group by model_number, design_level, prod_type, last_prod_step


    Check the latest Oracle DBA forums at:
    http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi

    Hope that helps,


  5. #5
    Join Date
    May 2002
    Posts
    2
    You can use decode inside the count function to come up with multiple counts in a single select on the same line

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Not in this case, unless you can prove it.... with code !


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •