#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    1
    Rep Power
    0

    Count and Group Query Help!


    I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

    SELECT
    mv.R1_State,
    mv.subscriber_id,
    mv.plan_code,
    pm.description,
    mv.line_of_business,
    to_char(mv.effective_date, 'yyyy/mm/dd') as "Effective Date",
    to_char(mv.term_date,'yyyy/mm/dd') as "Term Date"

    FROM windsoradm.member_mv mv
    Join windsoradm.plan_master pm
    on pm.plan_code= mv.plan_code

    Where mv.term_date >= to_date('01/01/2011','MM/DD/YYYY')
    AND mv.R1_State IN ('AL','GA','LA','MT','WA','OK')

    ORDER BY 1,4


    But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???

    Select
    Count(pm.description),
    mv.R1_State

    FROM windsoradm.member_mv mv
    Join windsoradm.plan_master pm
    on pm.plan_code= mv.plan_code


    Where mv.R1_State IN ('AL','GA','LA','MT','WA','OK')
    AND mv.term_date >= to_date('01/01/2011','MM/DD/YYYY')

    Group by mv.r1_state

    Thanks for any help with this.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    850
    Rep Power
    387

    Cool


    Originally Posted by liro1966
    I have a report I created and I need to get a Total Count by Plan Code Description for Each State.
    ... etc ...
    If it's not something like this, then you need to post source data and expected results:
    Code:
    SQL> comp count of job on deptno
    SQL> comp count of empno on job
    SQL> break on report on deptno on job
    SQL> select
      2  deptno, job, empno, ename, sal
      3  --count(*) over (partition by deptno, job order by empno) tot_cnt
      4  from emp
      5  where deptno in ('10','30')
      6  order by 1,2
      7  /
    
        DEPTNO JOB                              EMPNO ENAME                                 SAL
    ---------- --------------------------- ---------- ------------------------------ ----------
            10 CLERK                             7934 MILLER                               1300
               *************************** ----------
               count                                1
               MANAGER                           7782 CLARK                                2450
               *************************** ----------
               count                                1
               PRESIDENT                         7839 KING                                 5000
               *************************** ----------
               count                                1
    ********** ---------------------------
    count                                3
            30 CLERK                             7900 JAMES                                 950
               *************************** ----------
               count                                1
               MANAGER                           7698 BLAKE                                2850
               *************************** ----------
               count                                1
               SALESMAN                          7654 MARTIN                               1250
                                                 7499 ALLEN                                1600
                                                 7844 TURNER                               1500
                                                 7521 WARD                                 1250
               *************************** ----------
               count                                4
    ********** ---------------------------
    count                                6
    
    9 rows selected.

IMN logo majestic logo threadwatch logo seochat tools logo