Home » SQL & PL/SQL » SQL & PL/SQL » multiple insertion problem
multiple insertion problem [message #408762] Wed, 17 June 2009 13:23 Go to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Hello,


I wanna calculate the average when inserting data into a table with this formula:
((avg(mark) * 3)+mark_of_type_'T')/4
so no more than one mark of type 'T' is permitted for a schoolboy(this is checked in a trigger).
But how to calculate the whole average using that formula?
i tried with
insert into avg_marks (schoolboy_id, avg_mark)
(select schoolboy_id, (avg(mark)*3+(select mark from marks where mark_type='T')/4)
from marks
group by schoolboy_id)


but i got ORA-01427: single-row subquery returns more than one row

and this is normal, but how can i calculate that for each schoolboy?
Regards,
Re: multiple insertion problem [message #408765 is a reply to message #408762] Wed, 17 June 2009 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>how can i calculate that for each schoolboy?
By modifying subquery so it only returns 1 row (at a time).
Re: multiple insertion problem [message #408766 is a reply to message #408762] Wed, 17 June 2009 13:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Not tested.
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(mark) * 3 + (SELECT mark 
                            FROM   marks 
                            WHERE  mark_type = 'T' 
                                   AND schoolboy_id = m1.schoolboy_id) / 4) 
 FROM     marks m1 
 GROUP BY schoolboy_id);
(Or)
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(mark) * 3 + Sum(Decode(mark_type,'T',mark, 
                                                0)) / 4) 
 FROM     marks 
 GROUP BY schoolboy_id);
By
Vamsi
Re: multiple insertion problem [message #408769 is a reply to message #408766] Wed, 17 June 2009 13:54 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Wed, 17 June 2009 21:38
Not tested.
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(mark) * 3 + (SELECT mark 
                            FROM   marks 
                            WHERE  mark_type = 'T' 
                                   AND schoolboy_id = m1.schoolboy_id) / 4) 
 FROM     marks m1 
 GROUP BY schoolboy_id);
(Or)
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(mark) * 3 + Sum(Decode(mark_type,'T',mark, 
                                                0)) / 4) 
 FROM     marks 
 GROUP BY schoolboy_id);
By
Vamsi

Hello,
thanks for reply
after calculations, i got for example 7 and 9, but currect is 7.5 and 9.5
i modified some paranthesis, the formula is:
((avg(mark)*3)+mark_type_'T')/4
but why i got 7 instead of 7.5 and 9 instead of 9.5?

Regards,
Re: multiple insertion problem [message #408770 is a reply to message #408769] Wed, 17 June 2009 13:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Without knowing your values, how can anyone tell why it is giving those results?
Read the Forum Guide before posting.

By
Vamsi
Re: multiple insertion problem [message #408772 is a reply to message #408770] Wed, 17 June 2009 14:19 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Wed, 17 June 2009 21:59
Without knowing your values, how can anyone tell why it is giving those results?
Read the Forum Guide before posting.

By
Vamsi

ok, sorry..
just for test:
create table A (
schoolboy_id integer,
mark integer,
mark_type char(1))
/

insert into A values (50, 9, 'N');
insert into A values (50, 7, 'N');
insert into A values (50, 6, 'T');
insert into A values (88, 9, 'N');
insert into A values (88, 7, 'N');
insert into A values (88, 10, 'T');

and with
select schoolboy_id, ((avg(mark)*3)+(select mark from A where mark_type='T' and schoolboy_id=m1.schoolboy_id))/4 from A m1 group by schoolboy_id;

i got

SCHOOLBOY_ID ((AVG(MARK)*3)+(SELECTMARKFROM
--------------------------------------- ------------------------------
88 9
50 7

for id=50 it must be (avg(9,7)*3)+6)/4 and the result expected is 30/4 which is 7.5
am i right?

Thanks
Re: multiple insertion problem [message #408773 is a reply to message #408772] Wed, 17 June 2009 14:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Nowhere you DID tell us that the avg is only for the non 'T' mark_type.
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(Decode(mark_type,'T',NULL, -- avg won't get calculated for null rows.
                                mark)) * 3 + Sum(Decode(mark_type,'T',mark, 
                                                                  0))) / 4 -- changed the paranthesis.
 FROM     marks 
 GROUP BY schoolboy_id);
By
Vamsi
Re: multiple insertion problem [message #408774 is a reply to message #408773] Wed, 17 June 2009 14:46 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Wed, 17 June 2009 22:39
Nowhere you DID tell us that the avg is only for the non 'T' mark_type.
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) 
(SELECT   schoolboy_id, 
          (Avg(Decode(mark_type,'T',NULL, -- avg won't get calculated for null rows.
                                mark)) * 3 + Sum(Decode(mark_type,'T',mark, 
                                                                  0))) / 4 -- changed the paranthesis.
 FROM     marks 
 GROUP BY schoolboy_id);
By
Vamsi

oh, my bad Uh Oh
i'm too sleepy now.
Thanks for help

Regards,
Re: multiple insertion problem [message #408786 is a reply to message #408773] Wed, 17 June 2009 15:47 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Vamsi, is it possible that if the returned avg is <5, those records corresponding to the final avg<5 to be inserted in another table?

Regards
Re: multiple insertion problem [message #408811 is a reply to message #408786] Wed, 17 June 2009 22:54 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't get you.
Do you mean ((avg(mark) * 3)+mark_of_type_'T')/4 should be < 5 and avg(mark) < 5 also?

By
Vamsi
Re: multiple insertion problem [message #408861 is a reply to message #408811] Thu, 18 June 2009 02:01 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Thu, 18 June 2009 06:54
I didn't get you.
Do you mean ((avg(mark) * 3)+mark_of_type_'T')/4 should be < 5 and avg(mark) < 5 also?

By
Vamsi

just ((avg(mark) * 3)+mark_of_type_'T')/4 should be < 5. in this case those records should be inserted in another table.. but how?

[Updated on: Thu, 18 June 2009 02:01]

Report message to a moderator

Re: multiple insertion problem [message #408864 is a reply to message #408861] Thu, 18 June 2009 02:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use having clause
(Or)
use outer select and where clause.

By
Vamsi
Re: multiple insertion problem [message #408865 is a reply to message #408864] Thu, 18 June 2009 02:10 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Thu, 18 June 2009 10:06
Use having clause
(Or)
use outer select and where clause.

By
Vamsi

and how should i use having, because i need to use twice, not? once for >5 and once for <5. i need two selects?
Re: multiple insertion problem [message #408875 is a reply to message #408865] Thu, 18 June 2009 02:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Oh! You didn't mention that too...
You can use insert all and when. Read the documentation for this.

By
Vamsi
Re: multiple insertion problem [message #408877 is a reply to message #408875] Thu, 18 June 2009 02:41 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Thu, 18 June 2009 10:32
Oh! You didn't mention that too...
You can use insert all and when. Read the documentation for this.

By
Vamsi

something like this?
with t as(
(SELECT   schoolboy_id, 
          ((Avg(Decode(mark_type,'T',NULL,mark)) * 3 + Sum(Decode(mark_type,'T',mark, 
                                                                  0))) / 4 as MARK) > 5
)
INSERT INTO avg_marks 
           (schoolboy_id, 
            avg_mark) values (t.schoolboy_id, t.MARK)

but when that avg is < 5 ?

Regards,

[Updated on: Thu, 18 June 2009 02:41]

Report message to a moderator

Re: multiple insertion problem [message #408887 is a reply to message #408875] Thu, 18 June 2009 03:21 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
vamsi kasina wrote on Thu, 18 June 2009 10:32
Oh! You didn't mention that too...
You can use insert all and when. Read the documentation for this.

By
Vamsi

with t as(
(SELECT   schoolboy_id,
          ((Avg(Decode(mark_type,'T',NULL,mark)) * 3 
          + Sum(Decode(mark_type,'T',mark,0)) / 4))>5) MARK
from MARKS
)
INSERT INTO avg_marks
           (schoolboy_id,
            avg_mark) values (t.schoolboy_id, t.MARK)
 
ORA-00923: FROM keyword not found where expected

so in that select i cannot use >5 ?
Re: multiple insertion problem [message #408888 is a reply to message #408762] Thu, 18 June 2009 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
your brackets don't match up.

And you can't have a >5 in the select part - it makes no sense - the result of that would be a boolean.

That needs to be a having clause.
And for the sake of simplicity I'd write two insert/selects.

[Updated on: Thu, 18 June 2009 03:29]

Report message to a moderator

Re: multiple insertion problem [message #408889 is a reply to message #408887] Thu, 18 June 2009 03:27 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't say "with".
I said "INSERT ALL" and "WHEN".

By
Vamsi
Previous Topic: Getting date problem
Next Topic: Calculating time difference
Goto Forum:
  


Current Time: Thu Dec 08 02:20:54 CST 2016

Total time taken to generate the page: 0.09477 seconds