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?

mrotor
Who is Participating?

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
``````
0

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

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

mrotor
0

Data EngineerCommented:
you mean this.
``````select ItemCategory,ItemCost,ItemCategory *ItemCost Product
from yourtable
``````
0

Author Commented:
Sharath,
It should be itemcost * itemcost

For example, if I have 3 records with the following values:
3
8
2

3 * 8 = 24
24 * 2 = 48

How can I do this?
0

Data 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

Commented:
Replace 2 places of #m with your table name
0

Commented:
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.