Count , Per Week Group by [message #664615] |
Mon, 24 July 2017 15:51 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior 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 #664682 is a reply to message #664615] |
Fri, 28 July 2017 02:24 |
|
Barbara Boehmer
Messages: 9097 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 |
Bill B
Messages: 1971 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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Bill B wrote on Fri, 28 July 2017 05:15If 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 |
Bill B
Messages: 1971 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
|
|
|