0

enter image description here

  1. I want to set the value for task to be Support, Project, Development, Administrative duties and Lunch. Is there any way I can set it to default so that I don't have to change it. If I inserted another row in it would be completely different.

Regarding number 2, THIS HAS BEEN RESOLVED. The correct syntax is -

 UPDATE it_time_track SET Monday = (SELECT SUM(Monday) FROM it_time_track ) WHERE id = 7*
  1. As you can tell, this is just a round about way to make the table I want only visually but I cannot get it to function like how I want it.

I want the daily total to be the sum of the column's values. This is my attempt:

select sum(Monday) 
from it_time_track 
into 
Monday where id = 7 ;

Attempt 2:

Select sum(Monday) from it_time_track into Monday 
where PERCENTAGE like 
'Daily total' from it_time_track ;
Improve this question
6
  • If you want to sum the records grouped by day, then you should use GROUP BY. Commented Aug 8, 2018 at 4:16
  • Images and screenshots can be a nice addition to a post, but please make sure the post is still clear and useful without them. Don't post images of code or error messages. Instead copy and paste or type the actual code/message into the post directly. Commented Aug 8, 2018 at 5:19
  • Regarding point 1.: If I'm understanding it correctly, you want your TASK column to have default value 'Support, Project, Development ,Administrative duties and Lunch' during insert, if not otherwise chosen? Regarding point 2.: Do you want to select data to show as it is from the picture (with, in addition, having dana in MONDAY column that is the sum you wanted), or do you already have such data in table and you just want to update that table with summed data? Commented Aug 8, 2018 at 7:38
  • into clause comes after the query projection and before the from clause. Commented Aug 8, 2018 at 12:57
  • @GoranKutlaca You understood my problem regarding point 1 . I want my Task column to have the default values " Support, Project, Development ,Administrative duties and Lunch'" during insert, if not otherwise chosen . Commented Aug 8, 2018 at 14:03

2 Answers 2

Reset to default
1

Your desired table looks like it should be the output of a query, not a table itself. Take a look at this...

create table base_table (
    task varchar2(255) not null,
    day_of_week varchar2(255),
    hours number);

insert into base_table values ('Support', 'MONDAY', 10);
insert into base_table values ('Support', 'MONDAY', 7);
insert into base_table values ('Support', 'MONDAY', 5);
insert into base_table values ('Support', 'MONDAY', 1);
insert into base_table values ('UnScheduled Project', 'MONDAY', 1);
insert into base_table values ('Admin', 'MONDAY', 12);
insert into base_table values ('Out of Office', 'MONDAY', 8);
insert into base_table values ('~NULL~', 'MONDAY', 3);
insert into base_table values ('Support', 'TUESDAY', 5);
insert into base_table values ('Support', 'TUESDAY', 1);

select nvl(task, 'Total') as task,
    round(total_hours / first_value(total_hours) over (order by sort_order desc) * 100, 2) as pct_of_total,
    total_hours, monday, tuesday, wednesday, thursday, friday
from (select task, rank() over (order by task) as sort_order,
        sum(hours) as total_hours,
        sum(case when day_of_week = 'MONDAY' then hours else null end) as monday,
        sum(case when day_of_week = 'TUESDAY' then hours else null end) as tuesday,
        sum(case when day_of_week = 'WEDNESDAY' then hours else null end) as wednesday,
        sum(case when day_of_week = 'THURSDAY' then hours else null end) as thursday,
        sum(case when day_of_week = 'FRIDAY' then hours else null end) as friday
    from base_table
    group by grouping sets ((task), ()))
order by sort_order;

Output here: SQLFiddle

The inner query uses grouping sets to get the Total row. Then the outer query uses analytic function to grab the total row so you can calculate the pct of total. I did it for the whole week here, but you could duplicate that out for each day column if wanted.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you for your hard work. I see the result . The output is good , close to what I want but I dont understand how you did it .
I understand the case statements but then I am lost .
What is first_value ?
This was the most helpful answer .
first_value is an analytic function. You can think of it as a function that runs on the results. first_value gives me the first value for the column specified when the results are ordered the way specified. That is why I added the sort_order column in the inner query, so I could easily pull out the total line in the outer query.
|
1

Point 1:

You could've set defautl value when creating table, but you can also set it now, using:

 ALTER TABLE it_time_track
    MODIFY (task DEFAULT 'Support, Project, Development ,Administrative duties and Lunch');

That would set that columns' value to that string during insert, if no other string has been given.

With point 2, you've came up with an answer :)

Improve this answer

4 Comments

I want 5 rows each with a single value not the entire string and also this says invalid alter command .
[Code: 940, SQL State: 42000] ORA-00940: invalid ALTER command
@Naq_23 I no longer understand what you want to do... You cannot set several default values on a single column... And in what scenario do you plan to insert in your table?
I want to be able to do that . Thats why I posted the question . When I create a table i define columns , Is there a way for me to define rows . so if x is rows and y is columns then is there a way for me to define a value at point x,y .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.