a guest Jun 17th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. `--AVG used only in Select correctly does the average calculation from the detail (child) table SELECT sd.customer, AVG(score) AS avg_score_group from score_detail as sd
  2.         JOIN score_master as sm ON sd.customer = sm.customer
  3.         GROUP BY sd.customer
  4.     --Using AVG to actually update the master table with averages from child
  5.     table has incorrect calculations for some grouped records
  6.         UPDATE score_master
  7.         SET avg_score = t.avg_score_group
  8.         FROM (
  9.         SELECT AVG(score) AS avg_score_group from score_detail as sd
  10.         JOIN
  11.         score_master as sm
  12.         ON
  13.         sd.customer = sm.customer
  14.         GROUP BY sd.customer
  15.         ) AS t
  16.     --Let us explore master table
  17.         SELECT * FROM  score_master;
  18. --Table structures:
  19. CREATE TABLE [dbo].[score_detail](
  20. [customer] [float] NULL,
  21. [score] [float] NULL
  22. ) ON [PRIMARY]
  25. CREATE TABLE [dbo].[score_master](
  26. [customer] [float] NULL,
  27. [avg_score] [float] NULL
  28. ) ON [PRIMARY]
  29. -- Data to calculate average has 17 Decimal points:
  30. INSERT [dbo].[score_detail] ([customer], [score]) VALUES (2, -0.07216878364870323)`
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand