I need help with a SQL Query

Hi experts,
I have a table that has two fields, ItemCategory - varchar(50) ,and ItemCost - float.  I need to write T-SQL to:
1. Calculate the Product of all ItemCost values by ItemCategory.
2. Calculate the Median value of ItemCost by ItemCategory.
How can I do this?  

Thanks in advance,
mrotor
mainrotorAsked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
I guess you have your reason to required these product / median
below query should done it

;WITH CTE AS (SELECT ItemCategory, ItemCost, ROW_NUMBER() OVER (PARTITION BY ItemCategory ORDER BY ItemCost) RN FROM #m)
SELECT SubProduct.ItemCategory, SubProduct.Product, AVG(CTE.ItemCost) Median
FROM
(SELECT ItemCategory, EXP(SUM(LOG(ItemCost))) Product, COUNT(*) CN FROM #m GROUP BY ItemCategory) SubProduct
	INNER JOIN CTE ON SubProduct.ItemCategory = CTE.ItemCategory AND RN IN ( (CN+1)/2 , (CN+2)/2 )
GROUP BY SubProduct.ItemCategory, SubProduct.Product

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>ItemCost - float
please change to decimal(20,4), for example, float is a unprecise data type.

can you please clarify "product of itemcost" ... I presume you mean sum() ?

select ItemCategory, sum(itemcost) from yourtable group by itemcategory

but not sure if that logically makes any sense...

median: means the middle value of all the values:
see here the technique how to do, in general:
http://www.1keydata.com/sql/sql-median.html


0
 
mainrotorAuthor Commented:
angellll,
the The product is the result of multiplying numbers together. For example, the Product of 2 x 2 = 4.

mrotor
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
SharathData EngineerCommented:
you mean this.
select ItemCategory,ItemCost,ItemCategory *ItemCost Product
  from yourtable

Open in new window

0
 
mainrotorAuthor Commented:
Sharath,
It should be itemcost * itemcost

For example, if I have 3 records with the following values:
3
8
2
The answer should be 48

3 * 8 = 24
24 * 2 = 48

How can I do this?
0
 
SharathData EngineerCommented:
Why do you want that value? If you have more items and item cost is in hundreds or thousands, you will get a big number.
0
 
JoeNuvoCommented:
Replace 2 places of #m with your table name
0
 
vandalesmCommented:
You can also do this by creating a function that accepts ItemCategory parameter

declare @product float
set @product=1
select @product = @product * itemCost from YOURTABLE where itemCategory=@itemCategory
return @product

You can figure out the median.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.