How to make this query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make this query?

Author

Topic

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-14 : 11:35:16

I want to make query for average sales price, the records are like this

item, Sales_qty, UnitPrice
A, 10, 1000
A, 20, 1100
A, 5, 1500
B, 1, 1000
B, 2, 1200

and display like this
A, 35, 1128 (35 FROM total qty, 1128 from average sales price)
B, 3, 1133

Thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 11:39:47

How did you calculate average sales price?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-14 : 11:42:58

for item A
(10x1000 + 20x1100 + 5x1500) / 35

for item B
(1x1000+2x1200) / 3 = 3400 / 3 = 1133

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 11:45:07

[code]declare @t table
(
item varchar(10),
Sales_qty numeric(10,5),
UnitPrice numeric(10,5)
)

insert @t
select 'A', 10, 1000 union all
select 'A', 20, 1100 union all
select 'A', 5, 1500 union all
select 'B', 1, 1000 union all
select 'B', 2, 1200

select item, sum(Sales_qty) as Sales_qty, sum(UnitPrice*Sales_qty)*1.0/ sum(sales_qty) as AvgUnitPrice
from @t
group by item[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-14 : 11:49:43

Great!!, you are my savior...
thanks a lot

btw, why do I have to multiply with 1.0 ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 11:54:48

*1.0 part is only if you are storing data in fraction, else ignore it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-14 : 12:00:59

in my real case qty represent balance amount and may have zero value
SQL give message "Divide by zero error encountered"

How can I avoid this ? I dont want WHERE statement because of something else reason

thanks again

ps. lets say one more record
C, 0, 0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 12:05:25

If qty is zero, what you expect average sales price to be?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-14 : 12:15:11

that just an example, actually it is stock on hand in several warehouses
I want to display item, qty_on_hand, average_price, qty_purchase, average_purchase_price etc
for consolidated cogs purpose, it is possible one item doesnt have stock on hand but I still want to show that item.

it is solved now, I use case when statement..

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources