gufran gufran - 1 year ago 79
SQL Question

how to update table from another table with sum function?

i want copy data jumlah siswa from data_sekolah table to data_baru table with sum function (field jumlah siswa summed by field class and date)

i tried this query

update [data_baru] set [jumlah siswa]= SUM(DS.[jumlah siswa])
FROM [data_baru] DB
[data_sekolah] DS ON DB.[class]=DS.[class] and month(DB.[Date]) = month(DS.[Date])

and the message is

An aggregate may not appear in the set list of an UPDATE statement.

if i didn't use sum function i just can take top 1 from field jumlah siswa by class and date on data_sekolah table

Answer Source

Try with the below query..

      update DB
      set [jumlah siswa]=  SUM_jumlah siswa
      FROM [data_baru] DB
         ( Select class,month([Date]) Month
             ,SUM([jumlah siswa]) SUM_jumlah siswa
           From  [data_sekolah]
          Group by class,month([date]) ) DS 
         ON DB.[class]=DS.[class] 
             and month(DB.[Date]) =DS.Month)