1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to display no of records per day and total no record per week in a single query..

Discussion in 'SQL PL/SQL' started by Vicky, Feb 12, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hii..

    How to display no of records per day and total no record per week in a single query..

    my below query displays records per day.. how to fetch the total no records in this qry itself::

    select to_char(DOB,'DY'),count(1) from EMPLOYEES group by to_char(DOB,'DY');

    does it can be achieved usng Pivot?!
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Hello ,

    The query select to_char(DOB,'DY'),count(1) from EMPLOYEES group by to_char(DOB,'DY'); will display total count for the day i.e MON,TUE . If you are fetching more than one month or more than one year then all the data will be sum up. and display in the format Mon, Tue .... Can you please clarify what you are expecting as total no record per week.

    If you provide sample data or more elaboration on your requirement we could try to find the solution ...
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: how to display no of records per day and total no record per week in a single que

    Sure..

    SNO EMP_ID EMP_NAME SALARY PROCESS_DATE MANAGER_ID CITY
    --- ------ --------- ------ ------------------ ---------- ----------
    8 30 celin 5000 02-FEB-99 12:00:00 50 Chennai
    9 10 Dev 8000 30-MAR-92 12:00:00 10 Chennai
    10 50 elisa 9000 23-JAN-99 12:00:00 60 Delhi
    12 60 Dev 5231 21-MAR-96 12:00:00 80 Chennai
    7 12 bala 5000 13-OCT-14 01:02:00 20 Chennai
    15 10 Ram 5000 21-JAN-96 12:00:00 100 Delhi
    8 30 celin 8000 02-FEB-99 12:00:00 50 Delhi


    After using the below qry..

    select TO_CHAR(PROCESS_DATE,'DY'),COUNT(1) from EMPLOYEES group by TO_CHAR(PROCESS_DATE,'DY');


    TO_CHAR(PROCESS_DATE,'DY') COUNT(1)
    ----------------- --------
    TUE 2
    THU 1
    SUN 1
    MON 2
    SAT 1


    I wanna get the result as below..

    TO_CHAR(PROCESS_DATE,'DY') COUNT(1) Total
    ----------------- -------- --------
    TUE 2 7
    THU 1 7
    SUN 1 7
    MON 2 7
    SAT 1 7

    ....
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: how to display no of records per day and total no record per week in a single que

    It'd be better, If i cud get result like dis,.


    MON TUE WED THU FRI SAT SUN TOTAL
    --- --- --- --- --- --- --- ----- -----
    2 2 0 1 0 1 1 7
     
  5. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Hello Vicky ,

    Below logic will give the expected output for one week ..

    SELECT TO_CHAR (PROCESS_DATE, 'DY') Days_of_week
    ,COUNT (1) Count_for_day
    ,(SELECT SUM(Count_for_week)
    FROM
    (
    SELECT TO_CHAR (PROCESS_DATE, 'DY') Days_of_week
    ,COUNT(1) Count_for_week
    FROM EMPLOYEES
    GROUP BY TO_CHAR (PROCESS_DATE, 'DY')) ) Count_for_week
    FROM EMPLOYEES
    GROUP BY TO_CHAR (PROCESS_DATE, 'DY');

    Need some modification in the logic if you are fetching week wise data for

    Eg: Week 1 MON daycount week_count
    TUE daycount week_count
    Week 2 MON daycount week_count
    TUE daycount week_count
    As of now I am not sure about the efficiency of this logic . just I have thought to get output
     
    Vicky likes this.
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: how to display no of records per day and total no record per week in a single que

    Thanks RG Hegde.,

    Is it possible to get result as below..

    WEEK MON TUE WED THU FRI SAT SUN TOTAL
    --- --- --- --- --- --- --- --- -----
    1 2 2 0 1 0 1 1 7
    2 3 1 1 0 1 0 1 7
     
  7. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Hello Vicky,

    We could get that result , as of now I cannot remember how we could .. will check the possibility.
     
  8. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Hello Vicky,

    Can you please test below query to get the result in the format 'Year-Week' , Days_of_week,count, count_week
    SELECT TO_CHAR(em.PROCESS_DATE,'YYYY-IW') Week_of__year,TO_CHAR (em.PROCESS_DATE, 'DY') Days_of_week
    ,COUNT (1) Count_for_day
    ,Cfw.Count_for_week
    FROM EMPLOYEES em,
    (
    SELECT Weeks_of_Year,SUM(Count_for_week)Count_for_week
    FROM
    (
    SELECT TO_CHAR (em1.PROCESS_DATE, 'YYYY-IW')Weeks_of_Year,TO_CHAR (em1.PROCESS_DATE, 'DY') Days_of_week
    ,COUNT(1) Count_for_week
    FROM EMPLOYEES em1
    WHERE trunc(em1.PROCESS_DATE) between '1-Jan-2014' and '31-dec-2014'
    GROUP BY TO_CHAR (em1.PROCESS_DATE, 'YYYY-IW'),TO_CHAR (em1.PROCESS_DATE, 'DY'))
    GROUP BY Weeks_of_Year
    Order by 1) Cfw
    WHERE trunc(em.PROCESS_DATE) between '1-Jan-2014' and '31-dec-2014'
    AND TO_CHAR(em.PROCESS_DATE,'YYYY-IW')=Cfw.Weeks_of_Year
    GROUP BY TO_CHAR(em.PROCESS_DATE,'YYYY-IW'),TO_CHAR (em.PROCESS_DATE, 'DY'),Cfw.Count_for_week
    Order by 1;

    (here I have taken for 1 year)
    To get above output I believe we need to use Pivot concept . Need to check
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: how to display no of records per day and total no record per week in a single que

    Thanks Hegde..

    I also hope tat the thng can be achvd using pivot..

    And the o/p i cud get is..


    WEEK_OF__YEAR DAYS_OF_WEEK COUNT_FOR_DAY COUNT_FOR_WEEK
    ------------- ------------ ------------- --------------
    2014-42 MON 1 1
     
  10. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Vicky,

    I hope The query which I have provided will give the result as expected from you . If you need the data in Horizontal format . Please go through the Pivot concept and try to get the data in Horizontal format . If I get time I will try to provide you the query.
     
  11. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    Hello Vicky ,

    Please try the below query to display data in Horizontal format

    Code (SQL):
    SELECT *
    FROM
    SELECT   TO_CHAR(em.PROCESS_DATE,'YYYY-IW') Week_of__year,TO_CHAR (em.PROCESS_DATE, 'DY') Days_of_week
            ,COUNT (1)                     Count_for_day
           ,Cfw.Count_for_week
        FROM EMPLOYEES em,
     (
    SELECT Weeks_of_Year,SUM(Count_for_week)Count_for_week
    FROM
    (
    SELECT   TO_CHAR (em1.PROCESS_DATE, 'YYYY-IW')Weeks_of_Year,TO_CHAR (em1.PROCESS_DATE, 'DY') Days_of_week
                    ,COUNT(1)          Count_for_week
        FROM EMPLOYEES em1
        WHERE trunc(em1.PROCESS_DATE) BETWEEN '1-Jan-2014' AND '31-dec-2014'
    GROUP BY  TO_CHAR (em1.PROCESS_DATE, 'YYYY-IW'),TO_CHAR (em1.PROCESS_DATE, 'DY'))
    GROUP BY Weeks_of_Year
    ORDER BY 1)    Cfw
        WHERE trunc(em.PROCESS_DATE) BETWEEN '1-Jan-2014' AND '31-dec-2014'
        AND TO_CHAR(em.PROCESS_DATE,'YYYY-IW')=Cfw.Weeks_of_Year
    GROUP BY TO_CHAR(em.PROCESS_DATE,'YYYY-IW'),TO_CHAR (em.PROCESS_DATE, 'DY'),Cfw.Count_for_week
    ORDER BY 1)
    PIVOT (MAX(Count_for_day) FOR Days_of_week IN('MON','TUE','WED','THU','FRI','SAT','SUN'))
    ORDER BY 1
    I have tested this for Order creation. Please see the below output

    WEEK_OF__YEAR COUNT_FOR_WEEK 'MON' 'TUE' 'WED' 'THU' 'FRI' 'SAT' 'SUN'

    2014-01 10 10
    2014-02 12 8 4
    2014-05 4 1 3
    2014-06 2 2
    2014-07 1 1
    2014-10 22 5 17
    2014-11 6 4 2
    2014-12 3 3
     
  12. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Re: how to display no of records per day and total no record per week in a single que

    I have missed one '(' in the select 'select * from
    (....'

    Please make the correction from above query
     
    Vicky likes this.