CODEWITHSUNDEEP
sqlsql-server
gufran
gufran

Reputation: 111

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 (column [jumlah siswa] summed by column [class] and [date])

I tried this query

update [data_baru] 
set [jumlah siswa]= SUM(DS.[jumlah siswa])
from [data_baru] DB
inner join [data_sekolah] DS on DB.[class] = DS.[class] 
                             and month(DB.[Date]) = month(DS.[Date])

I get an error:

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 column jumlah siswa by class and date on data_sekolah table

Upvotes: 0

Views: 83

Answers (6)

artm
artm

Reputation: 8584

Use the following query:

UPDATE [data_baru] 
SET [jumlah siswa]= (SELECT SUM(DS.[jumlah siswa] 
                     FROM [data_sekolah] DS 
                     WHERE DB.[class]=DS.[class] 
                     AND month(DB.[Date]) = month(DS.[Date])
       FROM [data_baru] DB

https://support.microsoft.com/en-us/kb/90477

Upvotes: 1

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

The following query should do the trick!

UPDATE  DB  
SET     [jumlah siswa] = DS.Sum_jumlah_siswa  
FROM    [data_baru] AS DB  
CROSS APPLY  
    (   SELECT SUM(DS.[jumlah siswa]) AS Sum_jumlah_siswa   
        FROM [data_sekolah] AS DS   
        WHERE DB.[class]=DS.[class]   
            AND month(DB.[Date]) = month(DS.[Date])   
    ) AS DS

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

First do sum on the group (matching condition)

;    with temp as (
                    select DB.[class] as class , month(DB.[Date]) as date  ,
                    SUM(DS.[jumlah siswa]) as sum_jumlahsiswa
                    from [data_sekolah] DS group by DB.[class], month(DB.[Date])
         )

         update [data_baru] set [jumlah siswa] =sum_jumlahsiswa
            FROM [data_baru] DB
           INNER JOIN temp DS ON DB.[class]=DS.[class] and DB.[Date] = month(DS.[Date] )

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

Try This:

UPDATE DB SET [jumlah siswa]= t.tot
FROM [data_baru] DB
OUTER APPLY (SELECT SUM(DS.[jumlah siswa]) tot FROM [data_sekolah] DS 
WHERE DB.[class]=DS.[class] AND MONTH(DB.[Date]) = MONTH(DS.[Date]) 
GROUP BY DS.[class], MONTH(DS.[Date])) t

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query..

      update DB
      set [jumlah siswa]=  SUM_jumlah siswa
      FROM [data_baru] DB
       INNER JOIN
         ( 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)

Upvotes: 1

Bhavdip Tala
Bhavdip Tala

Reputation: 51

UPDATE  Process_Master
SET     cnt = a.ct
FROM    Process_Master
        INNER JOIN ( SELECT dbo.Process_Master.Process_Id ,
                            sum(1) AS ct
                     FROM   dbo.Employee_Process
                            INNER JOIN dbo.Process_Master ON dbo.Employee_Process.Process_Id = dbo.Process_Master.Process_Id
                     GROUP BY dbo.Process_Master.Process_Id
                   ) AS a ON Process_Master.Process_Id = a.Process_Id

Upvotes: 1

Related Questions