Update records with Count() function

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  July 26, 2012 at 5:29 am

  #278496

  Hi all,

  My SQL 'Table1' needs an update to a single column 'STCount'. The update needs to be applied

  grouped by 'Sales_Resp', 'Sales_Order', 'Sales_Type'.

  So,

  'Sales_Resp', 'Sales_Order', 'Sales_Type', 'STCount'

  TWells, 123, 4, 0

  TWells, 123, 4, 0

  TWells, 123, 4, 0

  TWells, 125, 4, 0

  TWells, 125, 4, 0

  ABaker, 129, 4, 0

  ABaker, 129, 4, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  Should become...

  'Sales_Resp', 'Sales_Order', 'Sales_Type', 'STCount'

  TWells, 123, 4, 3

  TWells, 123, 4, 3

  TWells, 123, 4, 3

  TWells, 125, 4, 2

  TWells, 125, 4, 2

  ABaker, 129, 4, 2

  ABaker, 129, 4, 2

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  Can anyone help please?

  Thanks in advance,

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  July 26, 2012 at 5:37 am

  #1517844

  DerbyNeal (7/26/2012)

  ---

  Hi all,

  My SQL 'Table1' needs an update to a single column 'STCount'. The update needs to be applied

  grouped by 'Sales_Resp', 'Sales_Order', 'Sales_Type'.

  So,

  'Sales_Resp', 'Sales_Order', 'Sales_Type', 'STCount'

  TWells, 123, 4, 0

  TWells, 123, 4, 0

  TWells, 123, 4, 0

  TWells, 125, 4, 0

  TWells, 125, 4, 0

  ABaker, 129, 4, 0

  ABaker, 129, 4, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  ABaker, 130, 9, 0

  Should become...

  'Sales_Resp', 'Sales_Order', 'Sales_Type', 'STCount'

  TWells, 123, 4, 3

  TWells, 123, 4, 3

  TWells, 123, 4, 3

  TWells, 125, 4, 2

  TWells, 125, 4, 2

  ABaker, 129, 4, 2

  ABaker, 129, 4, 2

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  ABaker, 130, 9, 4

  Can anyone help please?

  Thanks in advance,

  As this is dynamic, calculated info, I'd suggest using a view for this rather than persisting the info as part of the table - it's not a good design.

  But something like this should do it:

  ;with Counts as (select Sales_Resp, Sales_Order, Sales_Type, count(1) [Count] group by Sales_Resp, Sales_Order, Sales_Type)

  update t

  set STCount = Counts.Count

  from Table1 t join Counts on t.Sales_Resp = Counts.Sales_Resp and t.Sales_Order = Counts.Sales_Order and t.Sales_Type = Counts.Sales_Type

  ---

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 26, 2012 at 6:00 am

  #1517856

  DerbyNeal (7/26/2012)

  ---

  Hi all,

  My SQL 'Table1' needs an update to a single column 'STCount'. The update needs to be applied

  grouped by 'Sales_Resp', 'Sales_Order', 'Sales_Type'.

  I'd agree with Phil - it's not worth persisting as it's so trivial to calculate on-the-fly:

  SELECT

  Sales_Resp,

  Sales_Order,

  Sales_Type,

  STCount = COUNT(*) OVER (PARTITION BY Sales_Resp, Sales_Order, Sales_Type)

  FROM ...

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden