Solved

I need help with a SQL Query

Posted on 2011-03-10
8
238 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 142

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 36
T-SQL:  Collapsing 9 25
error in my cursor 5 33
Help to build a Proc... 6 19
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

810 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