avg calculation

• sravanb

  SSC-Addicted

  Points: 455

  More actions

  January 18, 2011 at 9:25 am

  #104025

  I have a situation where I need to calculate the avg sal only when I have a recent value.

  i.e,

  table:

  id reportingperiod sal

  1 jan 2000

  1 aug 2200

  2 jan 0

  2 aug 2000

  I want the result to be:

  id avg(sal)

  1 2100

  2 0

  I tried alot using case statements but I think I am missing some thing there..

  Please help me...

  Thanks.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 18, 2011 at 9:47 am

  #1274839

  Why not using AVG() ... GROUP BY?

  Edit: and what do you consider as being a "recent value"? You'd need a date to compare against, but I can't find one: The month name by itself can be from any year...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• SQLSmasher

  Right there with Babe

  Points: 777

  More actions

  January 18, 2011 at 9:50 am

  #1274844

  try this

  select id, avg(sal) from @temp

  where sal <> 0

  group by id

  having count(reportingperiod) = 2

  declare variable to store reportingperiod. in above example i have used constant 2

• sravanb

  SSC-Addicted

  Points: 455

  More actions

  January 18, 2011 at 9:54 am

  #1274847

  I can but I have a condition involved which is, when reportingperiod is jan and sal=0 then avg(sal) should be zero even if the reportingperiod apr sal has some value..

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 18, 2011 at 9:57 am

  #1274848

  SQLSmasher (1/18/2011)

  ---

  try this

  select id, avg(sal) from @temp

  where sal <> 0

  group by id

  having count(reportingperiod) = 2

  declare variable to store reportingperiod. in above example i have used constant 2

  WHa?

  non - zero Salary. I'm with ya.

  Group on the ID, I'm with ya.

  count( reportingperiod) = 2 with a variable to store ... nono, stop there. What? This gets, at best, an incredibly limited subset of the data. You want to go look deeper into the having clause and its usage.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 18, 2011 at 9:59 am

  #1274851

  sravanb (1/18/2011)

  ---

  I can but I have a condition involved which is, when reportingperiod is jan and sal=0 then avg(sal) should be zero even if the reportingperiod apr sal has some value..

  Can you post the real DDL to your table, as well as the full business rules as you know it?

  So far we have a vague reference to 'recent' entries, with no definition nor enforcable by the original ddl.

  Now we have a new business rule you're trying to solve, which wasn't mentioned at all originally.

  Take a breath, take a step back, and go through what you need from start to finish. It will be much easier for us to help you from that point with examples of the real structure and queries you've created so far.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 18, 2011 at 10:03 am

  #1274855

  additionally, we have a duplicate thread.

  @sravanb

  After reevaluating the issues as Craig recommended, please take the time and read the first article referenced in my (and Craigs) signature on how to ask questions on a forum.

  And, please, don't start another thread for the very same issue. Thank you.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• sravanb

  SSC-Addicted

  Points: 455

  More actions

  January 18, 2011 at 12:34 pm

  #1274938

  DDL for the table:

  sal(id int,reportingperiod varchar(10),salary int)

  Business rule:

  Average salary should be calculated only when if the employees have salary in reportingperiod month january.

  Table values:

  id reportingperiod sal

  1 jan 2000

  1 aug 2200

  2 jan 0

  2 aug 1000

  Result:

  id avg(sal)

  1 2100

  2 0

  Thank you.

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 18, 2011 at 1:05 pm

  #1274953

  sravanb (1/18/2011)

  ---

  DDL for the table:

  sal(id int,reportingperiod varchar(10),salary int)

  Business rule:

  Average salary should be calculated only when if the employees have salary in reportingperiod month january.

  This is untested since the ddl and data aren't consumable, but you want something like:

  SELECT

  id,

  CASE WHEN drvNonZeroLimiter.id IS NOT NULL

  THEN AVG( sal.salary)

  ELSE 0

  END AS AvgSalary

  FROM

  sal

  LEFT JOIN

  (SELECT DISTINCT

  id

  FROM

  sal

  WHERE

  reportingPeriod = 'Jan'

  AND salary <> 0

  ) AS drvNonZeroLimiter

  ON sal.id = drvNonZeroLimiter.id

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• sravanb

  SSC-Addicted

  Points: 455

  More actions

  January 18, 2011 at 2:13 pm

  #1274981

  Thanks craig!!