CASE to Check Over/Under AVG[Answered] RSS

• • Reply
  ReidMelSam

  Member

  39 Points

  404 Posts

  CASE to Check Over/Under AVG

  Feb 13, 2020 05:18 PM|ReidMelSam|LINK

  I have a query to attempt to obtain an AVG computed column of a table currency column "AllowedCharge."

  (SELECT AVG(AllowedCharge) as AVGCHG
  FROM GrocBills
  WHERE Code = @Code)

  ...there are several different "Codes" in the rows of the table (think of them as sku's), so I'm trying to AVG for individual code. Second thing I'm attempting, and this is where the problem is: trying to produce a field/column that will show whether the particular row is 'Above' or 'Below' the AVG for its particular "Code":

  SELECT 
  CASE
  	WHEN AllowedCharge > 'AVGCHG' THEN 'Over'
  	WHEN AllowedCharge < 'AVGCHG' THEN 'Under'
  	ELSE 'At Avg'
  END as Ov_Und
  FROM GrocBills
  

  ...so the error I'm getting when I add this feature is:

  Procedure spMedBills_GetAveregeAllowedChg, Line 13 [Batch Start Line 2]
  Error converting data type varchar to numeric

  In my mind, the output would look like:

  Code/SKU	Price	Code AVG	Ov_Und
  99000	10.00	15.00	Under
  99000	18.00	15.00	Over
  99000	17.00	15.00	Over
  99000	15.00	15.00	At AVG

  The end objective, once I learn this part, is to eliminate the "Code AVG" column and have the "Ov_Und" column show something like "2.00 Over Code AVG" etc.

  Not sure where my error is...learning to work through a CASE query. I'll really appreciate suggestion to fix this, or examples out there that are similar that I can learn from.

  Thanks much,

  RC
  
  
   

  "Look at it go, Homer; this one's gonna go for miles!"
• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: CASE to Check Over/Under AVG

  Feb 13, 2020 05:30 PM|limno|LINK

  declare @AVGCHG numeric(10,2)  
   SELECT @AVGCHG=AVG(AllowedCharge)  
  FROM GrocBills
  WHERE Code = @Code 
  
  SELECT 
  CASE
  	WHEN AllowedCharge > @AVGCHG THEN 'Over'
  	WHEN AllowedCharge < @AVGCHG THEN 'Under'
  	ELSE 'At Avg'
  END as Ov_Und
  FROM GrocBills

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  wmec

  Contributor

  6479 Points

  5815 Posts

  Re: CASE to Check Over/Under AVG

  Feb 14, 2020 10:12 AM|wmec|LINK

  Hi,
  You can have separate query like

  select ((select sum(price) from table0)/(select count(*) from table0)) as avg

  
  
  

  instead of having 'AVGCHG'!

  Many Thanks & Best Regards,
  Peter
  
• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: CASE to Check Over/Under AVG

  Feb 14, 2020 07:58 PM|limno|LINK

  You need to post your table GrocBills structure  and sample data with your expected result. Thanks.

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  ReidMelSam

  Member

  39 Points

  404 Posts

  Re: CASE to Check Over/Under AVG

  Feb 18, 2020 04:49 PM|ReidMelSam|LINK

  Thanks, this answer allowed me to progress to the next step...

  declare @AVGCHG numeric(18,2)
  declare @Diff numeric(18,2)
  SELECT @AVGCHG=AVG(AllowedCharge)
  FROM GrocBills
  WHERE Code = @Code
  
  SELECT Code, AllowedCharge,
  AllowedCharge - @AVGCHG as [@Diff] FROM GrocBills

  ...so that I could get at what I actually needed, a column showing the difference higher OR lower than the AVGCHG. Here is a depiction of the results:

  CODE/SKU	PRICE/AllowedCharge	DIFF	OVER_UNDER AVGCHG
  9931	15.00	+3.00	Over
  9931	11.00	-1.00	Under
  9931	12.00	0.00	At AVGCHG

  ...and of course since this is coming from a master table with multiple "Code/SKUs" it has to be averaged for the parameter "@Code".

  Thanks again for help.

  RC

  "Look at it go, Homer; this one's gonna go for miles!"