Oracle SQL - Sum and group data by week
Try this
SELECT to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW'),SUM(AMOUNT)
FROM YourTable
GROUP BY to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW')
FIDDLE DEMO
Output would be:
+-----+-------+--------+
|YEAR | WEEK | AMOUNT |
+-----+-------+--------+
|2013 | 11 | 18 |
|2013 | 13 | 3 |
+-----+-------+--------+
Oracle SQL - Sum and group data by daily, weekly and monthly. (With missing date, week, and month)
You need to generate all dates between your start and end date and join it with the result of your query.
Following query will give daily total amount. You need to change group by, order by and select clause if you want to get weekly and monthly result.
select all_dates.d as tran_timestamp,
sum(your_query.amt) as total_amt
from
(select date '2019-07-01' + level - 1 as d
from dual
connect by level <= (date '2019-09-30' - date '2019-07-01' ) + 1) all_dates
Left join
(SELECT trunc(tran_timestamp) as tran_timestamp , amt
FROM TRANSACTIONS
WHERE TRAN_STATUS = 'SUCCESS'
AND tran_timestamp >= TO_DATE('2019/07/01', 'yyyy/mm/dd')
AND tran_timestamp <= TO_DATE('2019/09/30','yyyy/mm/dd')) your_query
On (all_dates.d = tran_timestamp)
Group by all_dates.d
order by all_dates.d;
Cheers!!
Oracle SQL - group data by week between two date D1 and D2
You must first generate all weeks on-the-fly, as they are not in your database (i.e. there isn't a record for every week in the table). This is done with a recursive query. Then outer join your table.
with weeks(weekno, from_date, max_date) as
(
select 1 as weekno, date '2017-04-01' as from_date, date '2017-05-01' as max_date
from dual
union all
select weekno + 1 as weekno, from_date + 7 as from_date, max_date
from weeks
where from_date + 7 <= max_date
)
select weeks.weekno, count(mbw.meeting_date)
from weeks
left outer join nb_meeting_by_week mbw
on mbw.meeting_date >= weeks.from_date
and mbw.meeting_date < weeks.from_date + 7
and mbw.meeting_date <= weeks.max_date
group by weeks.weekno
order by weeks.weekno;
SELECT data grouped by WEEK in SQL
Sorry - just noticed that you said Oracle SQL, and my first answer was for SQL Server! The reason you are getting an error is that DATEPART is not an Oracle function. Instead, you can simply do math on the dates, using a known sunday (prior to a known first date in the DB table) as an anchor date:
SELECT
'30-DEC-2018' as "Known Sunday,
trunc((sysdate - to_date('30-DEC-2018')) / 7) as "Week Num",
to_date('30-DEC-2018')
+ (trunc((sysdate - to_date('30-DEC-2018')) / 7) * 7)"
FROM
dual
How to calculate total worktime per week [SQL]
You can find the start of the ISO week, which will always be a Monday, using TRUNC("DATE", 'IW').
So if, in the query, you GROUP BY the id and the start of the week TRUNC("DATE", 'IW') then you can SELECT the id and aggregate to find the SUM the WORKTIME column for each id.
Since this appears to be a homework question and you haven't attempted a query, I'll leave it at this to point you in the correct direction and you can complete the query.
Update
Now I need to create another column (lets call it WEEKLY_TIME) and populate it with values from the current output, so that Sep 1,3,4 (for ID=1) would all contain value 16.5, specifying that on that day (that is within the certain week) that person worked 16.5 in total. And for ID=2 it would then be a value of 10 for both Sep 2 and 4.
For this, if I understand correctly, you appear to not want to use aggregation functions and want to use the analytic version of the function:
select id,
"DATE",
trunc("DATE", 'IW') week,
worktime,
sum (worktime) OVER (PARTITION BY id, trunc("DATE", 'IW'))
AS weekly_time
from employees;
Which, for the sample data:
CREATE TABLE employees (ID, "DATE", WORKTIME) AS
SELECT 1, DATE '2014-09-01', 4 FROM DUAL UNION ALL
SELECT 2, DATE '2014-09-02', 6 FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-03', 5.5 FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-04', 7 FROM DUAL UNION ALL
SELECT 2, DATE '2014-09-04', 4 FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-09', 8 FROM DUAL;
Outputs:
ID DATE WEEK WORKTIME WEEKLY_TIME 1 2014-09-01 00:00:00 2014-09-01 00:00:00 4 16.5 1 2014-09-03 00:00:00 2014-09-01 00:00:00 5.5 16.5 1 2014-09-04 00:00:00 2014-09-01 00:00:00 7 16.5 1 2014-09-09 00:00:00 2014-09-08 00:00:00 8 8 2 2014-09-04 00:00:00 2014-09-01 00:00:00 4 10 2 2014-09-02 00:00:00 2014-09-01 00:00:00 6 10
Related Topics
Recursive Stored Functions in MySQL
SQL Server 2008 Insert with While Loop
How to Alter a Table for Identity Specification Is Identity SQL Server
SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword
What Is the Effect of Omitting Size in Nvarchar Declaration
Searching a Column Containing CSV Data in a MySQL Table for Existence of Input Values
It's Possible to Create a Rule in Preceding Rows in Sum
How to Change a Pg Column to Nullable True
Oracle: How to Implement a "Natural" Order-By in a SQL Query
SQL Server Case .. When .. in Statement
Read the Log File (*.Ldf) in SQL Server 2008
Postgresql Select Until Certain Total Amount Is Reached
Can Insert [...] on Conflict Be Used for Foreign Key Violations
How Does the in Predicate Work in SQL
SQL Server Rounding Error, Giving Different Values
Rails Active Query Order by Multiple Values in Specific Order