Solved

I need help with a SQL Query

Posted on 2011-03-10
8
247 Views
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,
mrotor
0
Comment
Question by:mainrotor
  • 2
  • 2
  • 2
  • +2
8 Comments
 
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:
http://www.1keydata.com/sql/sql-median.html


0
 

Author Comment

by:mainrotor
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 40

Expert Comment

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

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:mainrotor
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 40

Expert Comment

by:Sharath
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

by:
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

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35102335
Replace 2 places of #m with your table name
0
 
LVL 1

Expert Comment

by:vandalesm
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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