Solved

I need help with a SQL Query

Posted on 2011-03-10
8
261 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
[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
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 41

Expert Comment

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

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 41

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

Independent Software Vendors: 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

615 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