I need help with a SQL Query

Posted on 2011-03-10
Last Modified: 2012-05-11
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,
Question by:mainrotor
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

by:Guy Hengel [angelIII / a3]
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:


Author Comment

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

LVL 41

Expert Comment

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

Open in new window

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 35101309
It should be itemcost * itemcost

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

3 * 8 = 24
24 * 2 = 48

How can I do this?
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.
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
(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

LVL 11

Expert Comment

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

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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.

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.

Join & Ask a Question