Solved

# I need help with a SQL Query

Posted on 2011-03-10
256 Views
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
0
Question by:mainrotor
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
• 2
• 2
• +2

LVL 143

Expert Comment

ID: 35100959
>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 Comment

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

mrotor
0

LVL 41

Expert Comment

ID: 35101260
you mean this.
``````select ItemCategory,ItemCost,ItemCategory *ItemCost Product
from yourtable
``````
0

Author Comment

ID: 35101309
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

LVL 41

Expert Comment

ID: 35101376
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

LVL 11

Accepted Solution

JoeNuvo earned 500 total points
ID: 35102326
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

LVL 11

Expert Comment

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

LVL 1

Expert Comment

ID: 35111100
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
###### Suggested Courses
Course of the Month3 days, 8 hours left to enroll

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.