This discussion is archived
8 Replies Latest reply: Feb 28, 2011 10:55 AM by 840992 RSS

Grouping SQL statement

840992 Newbie
Currently Being Moderated
I need help with rewriting this query to get the output I wanted. Any help would be grealy appreciated.

Here I am trying to get the total number of master accounts and non master accounts created per month. With the below query I am getting the data, but I have to do lots of coding to display the data in a tabular/chart form on the web.

select last_day(add_months(trunc(created),-1))+1 as created, master, count(*) as countnumber
from web_users
group by last_day(add_months(trunc(created),-1))+1, master;

Right now it returns the data


Created Master Countnumber*

01/01/2004 Y 100
01/01/2004 N 120
02/01/2004 Y 100
02/01/2004 N 120
03/01/2004 Y 100
04/01/2004 Y 100
04/01/2004 N 120

I need data below output format. It will be easy for me to display the data on the web

Created Master1 Countnumber1 Master2 Countnumber2*

01/01/2004 Y 100 N 120
02/01/2004 Y 100 N 120
03/01/2004 Y 100 N 0
04/01/2004 Y 100 N 120
  • 1. Re: Grouping SQL statement
    Tubby Guru
    Currently Being Moderated
    837989 wrote:
    I need help with rewriting this query to get the output I wanted. Any help would be grealy appreciated.

    Here I am trying to get the total number of master accounts and non master accounts created per month. With the below query I am getting the data, but I have to do lots of coding to display the data in a tabular/chart form on the web.

    select last_day(add_months(trunc(created),-1))+1 as created, master, count(*) as countnumber
    from web_users
    group by last_day(add_months(trunc(created),-1))+1, master;

    Right now it returns the data


    Created Master Countnumber*

    01/01/2004 Y 100
    01/01/2004 N 120
    02/01/2004 Y 100
    02/01/2004 N 120
    03/01/2004 Y 100
    04/01/2004 Y 100
    04/01/2004 N 120

    I need data below output format. It will be easy for me to display the data on the web

    Created Master1 Countnumber1 Master2 Countnumber2*

    01/01/2004 Y 100 N 120
    02/01/2004 Y 100 N 120
    03/01/2004 Y 100 N 0
    04/01/2004 Y 100 N 120
    Is the column you need to pivot on constrained to at most 2 values (Y or N) ?
  • 2. Re: Grouping SQL statement
    789895 Expert
    Currently Being Moderated
    Hi,

    Try this not tested
    with t as (
    select '01/01/2004' as dt, 'Y' as status, 100 as val from dual union all 
    select '01/01/2004' ,'N', 120 from dual union all
    select '02/01/2004', 'Y', 100 from dual union all
    select '02/01/2004', 'N', 120 from dual union all
    select '03/01/2004', 'Y', 100 from dual union all
    select '04/01/2004', 'Y', 100 from dual union all
    select '04/01/2004', 'N', 120 from dual)
    select dt, max(status) stat, max(val) v, case when min(status) =  max(status) then 'N' else min(status) end stat1, case when  min(val)= max(val) then 0 else min(val) end v1 from t group by dt order by dt
    cheers

    VT
  • 3. Re: Grouping SQL statement
    Ganesh Srivatsav Guru
    Currently Being Moderated
    May be this?
    SQL> WITH TBL
      2       AS (SELECT       SYSDATE - 23 + LEVEL created, 'Y' MASTER, 100 counternumber
      3                 FROM   DUAL
      4           CONNECT BY   LEVEL <= 3
      5           UNION ALL
      6           SELECT       SYSDATE - 23 + LEVEL created, 'N' MASTER, 120 counternumber
      7                 FROM   DUAL
      8           CONNECT BY   LEVEL <= 3)
      9  SELECT   *
     10    FROM   tbl;
    
    CREATED   M COUNTERNUMBER
    --------- - -------------
    01-FEB-11 Y           100
    02-FEB-11 Y           100
    03-FEB-11 Y           100
    01-FEB-11 N           120
    02-FEB-11 N           120
    03-FEB-11 N           120
    
    6 rows selected.
    
    SQL> WITH TBL
      2       AS (SELECT       SYSDATE - 23 + LEVEL created, 'Y' MASTER, 100 counternumber
      3                 FROM   DUAL
      4           CONNECT BY   LEVEL <= 3
      5           UNION ALL
      6           SELECT       SYSDATE - 23 + LEVEL created, 'N' MASTER, 120 counternumber
      7                 FROM   DUAL
      8           CONNECT BY   LEVEL <= 3)   
      9  SELECT   tbl1.created
     10          ,tbl1.MASTER
     11          ,tbl1.counternumber
     12          ,tbl2.MASTER
     13          ,tbl2.counternumber
     14    FROM   TBL TBL2, TBL TBL1
     15   WHERE       tbl1.MASTER = 'Y'
     16           AND tbl2.MASTER = 'N'
     17           AND TRUNC (tbl1.created) = TRUNC (tbl2.created);
    
    CREATED   M COUNTERNUMBER M COUNTERNUMBER
    --------- - ------------- - -------------
    01-FEB-11 Y           100 N           120
    02-FEB-11 Y           100 N           120
    03-FEB-11 Y           100 N           120
    
    SQL> 
  • 4. Re: Grouping SQL statement
    RobvanWijk Oracle ACE
    Currently Being Moderated
    SQL> create table web_users (created,master)
      2  as
      3   select case
      4          when level <= 220 then date '2004-01-01'
      5          when level <= 440 then date '2004-02-01'
      6          when level <= 540 then date '2004-03-01'
      7          else date '2004-04-01'
      8          end
      9        , case
     10          when level <= 100 then 'Y'
     11          when level <= 220 then 'N'
     12          when level <= 320 then 'Y'
     13          when level <= 440 then 'N'
     14          when level <= 540 then 'Y'
     15          when level <= 640 then 'Y'
     16          else 'N'
     17          end
     18     from dual
     19  connect by level <= 760
     20  /
    
    Table created.
    
    SQL> select last_day(add_months(trunc(created),-1))+1 as created
      2       , master
      3       , count(*) as countnumber
      4    from web_users
      5   group by last_day(add_months(trunc(created),-1))+1
      6       , master
      7   order by created
      8       , master
      9  /
    
    CREATED             M COUNTNUMBER
    ------------------- - -----------
    01-01-2004 00:00:00 N         120
    01-01-2004 00:00:00 Y         100
    01-02-2004 00:00:00 N         120
    01-02-2004 00:00:00 Y         100
    01-03-2004 00:00:00 Y         100
    01-04-2004 00:00:00 N         120
    01-04-2004 00:00:00 Y         100
    
    7 rows selected.
    
    SQL> select trunc(created,'mm') created
      2       , 'Y' master1
      3       , count(case master when 'Y' then 1 end) countnumber1
      4       , 'N' master2
      5       , count(case master when 'N' then 1 end) countnumber2
      6    from web_users
      7   group by trunc(created,'mm')
      8   order by created
      9  /
    
    CREATED             M COUNTNUMBER1 M COUNTNUMBER2
    ------------------- - ------------ - ------------
    01-01-2004 00:00:00 Y          100 N          120
    01-02-2004 00:00:00 Y          100 N          120
    01-03-2004 00:00:00 Y          100 N            0
    01-04-2004 00:00:00 Y          100 N          120
    
    4 rows selected.
    Regards,
    Rob.
  • 5. Re: Grouping SQL statement
    840992 Newbie
    Currently Being Moderated
    Yes. This is exactly what I was looking. Thanks so much.
  • 6. Re: Grouping SQL statement
    840992 Newbie
    Currently Being Moderated
    Also I been trying to get the total number of master accounts and non master accounts created per WEEK for a given dates. Is it possible to get the results?
  • 7. Re: Grouping SQL statement
    803898 Explorer
    Currently Being Moderated
    SQL> select trunc(created,'WW') created
      2       , 'Y' master1
      3       , count(case master when 'Y' then 1 end) countnumber1
      4       , 'N' master2
      5       , count(case master when 'N' then 1 end) countnumber2
      6    from web_users
      7   group by trunc(created,'WW')
      8   order by created;
     
    CREATED     MASTER1                          COUNTNUMBER1 MASTER2                          COUNTNUMBER2
    ----------- -------------------------------- ------------ -------------------------------- ------------
    1/1/2004    Y                                         100 N                                         120
    1/29/2004   Y                                         100 N                                         120
    2/26/2004   Y                                         100 N                                           0
    4/1/2004    Y                                         100 N                                         120
     
  • 8. Re: Grouping SQL statement
    840992 Newbie
    Currently Being Moderated
    I modified the query to get the weekly count ( Monday - Sunday) for given date range. With the below query I am getting the correct weekly count. but I am trying to get the date 01/12/2011 instead of the 01/10/2011 in first row.

    select (next_day(trunc(created), 'MON')-7) created, 'Y' master1
    , count(case master when 'Y' then 1 end) countnumber1
    , 'N' master2
    , count(case master when 'N' then 1 end) countnumber2
    from web_users
    WHERE CREATED >= to_date('12-JAN-2011')and created < to_date('26-JAN-2011')
    group by (next_day(trunc(created), 'MON')-7)
    order by (next_day(trunc(created), 'MON')-7);

    created   Master1 CountNumber 1 Master2 CountNumber 2

    01/10/2011 Y 10 N 15
    01/17/2011 Y 15 N 12
    01/24/2011 Y 10 N 10

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points