Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Unanswered: count within count

    I have a table (lets call it X with colums like

    oats_order_id number id
    event_type char can be 'DS','RT','EX','CL'
    Each unique oats_order_id row has an event type of 1 of the 4 values above

    For each unique oats_order_id, I need to summarize
    1) count total rec's
    2) group the count by event type

    Output would look like this

    Sample Results Output:

    Code:
    OATS_ORDER_ID	TOTAL REC	DS CT	RT CT	EX CT	CL CT	   
    1189635264	107	90	106	0	0

    How would I write the sql to get this

    I tried

    Code:
    select OATS_ORDER_ID,
    count(OATS_ORDER_ID),
    event_type,
    count(event_type)
    from X
    group by OATS_ORDER_ID, event_type
    order by OATS_ORDER_ID, event_type;
    but that is not exactly correct

    anyone ?

    thanks in advance
    Code:
    select * from client where clue > 0;
    0 rows returned

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Based on the requirement stated in words, I would query with something like
    Code:
    SELECT oats_order_id, COUNT(*) total_rec,
      COUNT(CASE WHEN event_type = 'DS' THEN 1 END) ds_cnt,
      SUM(CASE WHEN event_type = 'RT' THEN 1 ELSE 0 END) rt_cnt,
      <etc.; choose any of those expressions you like more>
    FROM x
    GROUP BY oats_order_id
    ORDER BY oats_order_id
    in words, in every column count only rows with corresponding EVENT_TYPE.

    However I am not sure whether I understood it correctly, as the counts in your sample result set do not correspond (107 < 90+106+0+0).

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Perhaps the rollup function might help

    Code:
    select oats_order_id, event_type, count(*)
    from x
    group by oats_order_id,
    rollup(event_type)

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Thanks flyboy and Skywriter

    I actually got this far on my own

    Code:
    select oats_order_id,
    sum (ooid_count) over (partition by oats_order_id order by event_type ROWS UNBOUNDED PRECEDING) as total_count,
    event_type,
    ds_count,
    rt_count,
    ex_count,
    cl_count
    from (
       select OATS_ORDER_ID,
       count(OATS_ORDER_ID) as ooid_count,
       event_type,
       decode (event_type,'DS',count(event_type),null) as ds_count,
       decode (event_type,'RT',count(event_type),null) as rt_count,
       decode (event_type,'EX',count(event_type),null) as ex_count,
       decode (event_type,'CL',count(event_type),null) as cl_count
       from X
       group by OATS_ORDER_ID,event_type
       order by OATS_ORDER_ID, event_type)
    group by OATS_ORDER_ID,ooid_count,event_type,ds_count,rt_count,ex_count,cl_count
    order by oats_order_id;
    Output looks like this:

    OATS_ORDER TOTAL_CT EV DS_CNT RT_CNT EX_CNT CL_COUNT
    ------------ ----------- -- ---------- ---------- ---------- ----------
    1091658620 82 RT 26
    1091658634 1 DS 1
    1091658775 235 DS 235
    1091658775 354 RT 119


    But for the same oats_order_id (i.e. 1091658775) I waould rather see output like this:

    OATS_ORDER TOTAL_CT EV DS_CNT RT_CNT EX_CNT CL_COUNT
    ------------ ----------- -- ---------- ---------- ---------- ----------
    1091658775 354 DS 235
    1091658775 354 RT 119



    I am so close -- it hurts :-)

    Code:
    select * from client where clue > 0;
    0 rows returned

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    CLOSE, TRY


    Code:
       select OATS_ORDER_ID,
       count(OATS_ORDER_ID) as ooid_count,
       event_type,
       SUM(decode (event_type,'DS',1,0)) as ds_count,
       SUM(decode (event_type,'RT',1,0)) as rt_count,
       SUM(decode (event_type,'EX',1,0)) as ex_count,
       SUM(decode (event_type,'CL',1,0)) as cl_count
       from X
       group by OATS_ORDER_ID,event_type
       order by OATS_ORDER_ID, event_type;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Just a few remarks to the code:
    - the partial counts in the inner query seem unnecessary, as they are either total sum or NULL; the same goes with the ORDER BY clause
    - the GROUP BY clause in the outer query seems unnecessary too, as it contains all columns in the SELECT clause
    Both modifications will not affect the functionality, but I would change it for better readability and maintainability.
    - you use ORDER BY and windowing clause in SUM analytic, but showing the output you claim, you do not want it (both sums are the same); so get rid of them

    Then you end up with this (if your output format is definitive):
    Code:
    SELECT oats_order_id,
      SUM(ooid_count) OVER (PARTITION BY oats_order_id) total_count,
      event_type,
      CASE WHEN event_type = 'DS' THEN ooid_count END ds_count,
      CASE WHEN event_type = 'RT' THEN ooid_count END rt_count,
      CASE WHEN event_type = 'EX' THEN ooid_count END ex_count,
      CASE WHEN event_type = 'CL' THEN ooid_count END cl_count
    FROM (
       SELECT oats_order_id, event_type, COUNT(oats_order_id) ooid_count
       FROM x
       GROUP BY oats_order_id, event_type
    )
    ORDER BY oats_order_id, event_type;

Posting Permissions

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