irmira_fasyah 0 Report post Posted October 23, 2012 Salam & hai. saya tengah develop system penilaian utk projek tahun akhir saya. nak tnye utk query update join table database ade 2 table. table1 and table2. table1 utk mark. table2 utk totalmark yg diberi oleh penilai kepada yg dinilai. table1 [b]idpenilai idnilai tarikh mark [/b] Kan0001 Pen0001 2012-10-24 45 Kan0002 Pen0002 2012-10-24 54 Kan0003 Pen0001 2012-10-24 23 Kan0004 Pen0002 2012-10-24 53 Kan0005 Pen0001 2012-10-24 67 Kan0006 Pen0002 2012-10-24 45 Kan0007 Pen0001 2012-10-24 98 table2 [b]idnilai tarikh Totalmark [/b] Pen0001 2012-10-24 45 Pen0002 2012-10-24 54 everytime idpenilai evaluate idnilai, markah akan sentiasa update dlm table2 referred to idnilai. then part of update query yg sye buat :: [font=courier new,courier,monospace]update table2 a join table1 b on a.idnilai=b.idnilai set a.totalmark=sum(b.mark)/500*100 where year(a.tarikh)=year(b.tarikh)[/font] tapi bila run, keluar error 'invalid use of group function'. dh cari solution dari web2 lain, kena guna subquery tp makin confuse. hope ade yang dapat bantu.. thanks in advance Quote Share this post Link to post Share on other sites
akuccputsedut 147 Report post Posted October 24, 2012 [quote name='irmira_fasyah' timestamp='1351022015' post='1085825'] Salam & hai. saya tengah develop system penilaian utk projek tahun akhir saya. nak tnye utk query update join table database ade 2 table. table1 and table2. table1 utk mark. table2 utk totalmark yg diberi oleh penilai kepada yg dinilai. table1 [b]idpenilai idnilai tarikh mark [/b] Kan0001 Pen0001 2012-10-24 45 Kan0002 Pen0002 2012-10-24 54 Kan0003 Pen0001 2012-10-24 23 Kan0004 Pen0002 2012-10-24 53 Kan0005 Pen0001 2012-10-24 67 Kan0006 Pen0002 2012-10-24 45 Kan0007 Pen0001 2012-10-24 98 table2 [b]idnilai tarikh Totalmark [/b] Pen0001 2012-10-24 45 Pen0002 2012-10-24 54 everytime idpenilai evaluate idnilai, markah akan sentiasa update dlm table2 referred to idnilai. then part of update query yg sye buat :: [font=courier new,courier,monospace]update table2 a join table1 b on a.idnilai=b.idnilai set a.totalmark=sum(b.mark)/500*100 where year(a.tarikh)=year(b.tarikh)[/font] tapi bila run, keluar error 'invalid use of group function'. dh cari solution dari web2 lain, kena guna subquery tp makin confuse. hope ade yang dapat bantu.. thanks in advance [/quote] error tuh kalau tak silap kat sini ---> set a.totalmark=[b]sum(b.[/b]mark)/500*100 check balik code nih... kalau sempat karang aku tolong ko.. ko takleh nak henyak semua satu line.. ko tengok balik... ada sapa2 yang lain2 free bleh ler kira nya tolong adik nih jap... ok meeting bai Quote Share this post Link to post Share on other sites
Chuki2 32 Report post Posted October 25, 2012 Yup rasanya memang kena pakai subquery.. Cuba dalam SET to buat sub query untuk dapatkan SUM(b.mark) Quote Share this post Link to post Share on other sites
irmira_fasyah 0 Report post Posted October 25, 2012 ok dah solve dah.... mcm ni>> [font=courier new,courier,monospace]update table2 a [/font] [font=courier new,courier,monospace]join table1 b [/font] [font=courier new,courier,monospace]on a.idnilai=b.idnilai set a.totalmark =(select ((sum(mark)/500)*100) from table1 [/font] [font=courier new,courier,monospace]where idnilai='"+idnilai+"')[/font] [font=courier new,courier,monospace]where b.idnilai='"+idnilai+"' and year(a.tahun)=year(b.tarikh)[/font] so far jadi la..he any comment ?? persoalan kat cni, knape subquery tu xpyh declare 'dot' tu? cthnye:: [font='courier new', courier, monospace]"a.totalmark =(select ((sum(b.mark)/500)*100)[/font][font='courier new', courier, monospace]where b.idnilai='"+idnilai+"')[/font] Quote Share this post Link to post Share on other sites
Chuki2 32 Report post Posted October 25, 2012 Sebab sub query tu berlainan dari main query.... Jadi tak kena mengena dengan main query... Rasanya boleh je buat "dot", tapi kena buat variable la seperti table1 b.. Baru leh letak b "dot" mark dalam subquery Quote Share this post Link to post Share on other sites
irmira_fasyah 0 Report post Posted October 25, 2012 owh..ok2 terima kasih bnyk2 sebab respond [size=2]* & yg views * [/size] insyaAllah, akn ade further question about sql query..sbb mmg selalu stuck..haha Quote Share this post Link to post Share on other sites
Chuki2 32 Report post Posted October 26, 2012 [quote name='irmira_fasyah' timestamp='1351184370' post='1085901'] owh..ok2 terima kasih bnyk2 sebab respond [size=2]* & yg views * [/size] insyaAllah, akn ade further question about sql query..sbb mmg selalu stuck..haha [/quote] Okey gud luck bebeh Quote Share this post Link to post Share on other sites