Home » SQL & PL/SQL » SQL & PL/SQL » Count , Per Week Group by
Count , Per Week Group by [message #664615] Mon, 24 July 2017 15:51 Go to next message
azeem87
Messages: 96
Registered: September 2005
Location: dallas
Member
select count(*) , to_Char(create_dt,'MM/YYYY') from process_track_info  where create_dt between '01-JAN-2016' and '30-JUN-2017'   group by  to_Char(create_dt,'MM/YYYY')
order by to_Char(create_dt,'MM/YYYY')  ASC

COUNT(*) TO_CHAR(CREATE_DT,'MM/YYYY')
---------- ----------------------------
       147 01/2016                     
      2085 01/2017                     
      1767 02/2016                     
      4927 02/2017                     
       844 03/2016                     
      2381 03/2017                     
       581 04/2016                     
      2038 04/2017                     
      1671 05/2016                     
      5687 05/2017                     
      2586 06/2016                     
      2791 06/2017                     
      1277 07/2016                     
     18713 08/2016                     
      3828 09/2016                     
      7413 10/2016                     
      6377 11/2016                     
        63 12/2016                     

18 rows selected.


Table is Monthly partitioned on CREATE_DT.

how can i view data, for each Week, basically we need an output to see orders processed per Week from this table,
and can we order by the date ASC.


Tried this SQL for week wise, but it doesn't makes meaningful like which month/year this week is of...
select count(*) , to_Char(create_dt,'WW') from process_track_info  where create_dt between '01-JAN-2016' and '30-JUN-2017'   group by  to_Char(create_dt,'WW')
order by to_Char(create_dt,'WW')  ASC

  COUNT(*) TO_CHAR(CREATE_DT,'WW')
---------- -----------------------
        97 01                     
       671 02                     
       516 03                     
       758 04                     
       542 05                     
      1250 06                     
      1468 07                     
      2534 08                     
      1866 09                     
      1304 10                     
       550 11                     

Re: Count , Per Week Group by [message #664616 is a reply to message #664615] Mon, 24 July 2017 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
>where create_dt between '01-JAN-2016' and '30-JUN-2017'
Bad Coding by depending upon implicit data conversion between string & DATE.

You need to differentiate between Week 1 of 2016 & Week 1 of 2017.
You need to differentiate between Week 2 of 2016 & Week 2 of 2017.
You need to differentiate between Week 3 of 2016 & Week 3 of 2017.
etc.
Re: Count , Per Week Group by [message #664682 is a reply to message #664615] Fri, 28 July 2017 02:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8737
Registered: November 2002
Location: California, USA
Senior Member
select count(*), to_char(create_dt,'YYYY') yyyy, to_Char(create_dt,'WW') ww 
from   process_track_info  
where  create_dt between to_date('01-JAN-2016','dd-MON-yyyy') 
                     and to_date('30-JUN-2017','dd-MON-yyyy')   
group  by to_char(create_dt,'YYYY'), to_Char(create_dt,'WW')
order  by to_char(create_dt,'YYYY') ASC, to_Char(create_dt,'WW') ASC;
Re: Count , Per Week Group by [message #664685 is a reply to message #664682] Fri, 28 July 2017 07:15 Go to previous messageGo to next message
Bill B
Messages: 1687
Registered: December 2004
Senior Member
If I had to break out counts by week I would do the following

SELECT COUNT (*),
         TRUNC(create_dt, 'WW') Ww
    FROM process_track_info
   WHERE create_dt BETWEEN TO_DATE ('01-JAN-2016', 'dd-MON-yyyy')
                       AND TO_DATE ('30-JUN-2017', 'dd-MON-yyyy')
GROUP BY TRUNC(create_dt, 'WW')
ORDER BY TRUNC(create_dt, 'WW') ASC;
Re: Count , Per Week Group by [message #664686 is a reply to message #664685] Fri, 28 July 2017 07:27 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
Bill B wrote on Fri, 28 July 2017 05:15
If I had to break out counts by week I would do the following

SELECT COUNT (*),
         TRUNC(create_dt, 'WW') Ww
    FROM process_track_info
   WHERE create_dt BETWEEN TO_DATE ('01-JAN-2016', 'dd-MON-yyyy')
                       AND TO_DATE ('30-JUN-2017', 'dd-MON-yyyy')
GROUP BY TRUNC(create_dt, 'WW')
ORDER BY TRUNC(create_dt, 'WW') ASC;
IMO, COUNT(*) for WEEK=4 combines values for both 2016 & 2017; which is not correct results
Re: Count , Per Week Group by [message #664687 is a reply to message #664686] Fri, 28 July 2017 07:31 Go to previous message
Bill B
Messages: 1687
Registered: December 2004
Senior Member
No it doesn't. The TRUNC function using WW returns the first day of the week that the date is in, which is why I like using trunc. The return value is NOT a week number. It is an actual date. 2016 and 2017 will be 2 separate dates for week 4

[Updated on: Fri, 28 July 2017 07:32]

Report message to a moderator

Previous Topic: foreign key conflict issue
Next Topic: Weeks, Quarters, and Counts
Goto Forum:
  


Current Time: Mon Sep 25 21:09:41 CDT 2017

Total time taken to generate the page: 0.06956 seconds