sum involve group by or subquery

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

sum involve group by or subquery

Author

Topic

mrpenang
Starting Member

3 Posts

Posted - 2011-11-22 : 08:09:12

Hi All,

My item table has two columns as below:
sale_id price
1 14.88
1 5.18
1 10.35
2 6.9
3 19.84
4 13.8

I need to perform a calculation to get the sum of price to nearest 5cents where group row by row according to sale id.

It means I would like to calculate (14.88+5.18+10.35) round to nearest 5cents + 6.9 round to nearest 5cents + 19.84 round to nearest 5cents + 13.8 round to nearest 5cents.

I used:
SELECT sum(round(price*20)/20)
FROM item;
could not give me correct result due to sql round it to nearest 5 cents for each row, then only sum up the total

and
I used
SELECT (round(sum(price)*20))/20
FROM item;
could not give me correct result due to it add up all price then only round it to nearest 5 cents

I guess it need to involve sub query but I do not know how to do it.

Please help.

Thanks.
Eric

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-22 : 08:22:20

[code]
select round(sum(tot_price) * 20, 0) / 20
from (
select sales_id, tot_price = round(sum(price) * 20, 0 ) / 20
from item
group by sales_id
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-22 : 08:22:45

Use the rounding after you do the sum as in:


SELECT ROUND(SUM(price)/5,2)*5

mrpenang
Starting Member

3 Posts

Posted - 2011-11-22 : 08:31:09

khtan,

I have syntax error from your guide.

please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-22 : 08:34:08

what is the error message ?

KH
[spoiler]Time is always against us[/spoiler]

mrpenang
Starting Member

3 Posts

Posted - 2011-11-22 : 09:30:02

managed to figure out.

I changed to

select round(sum(tprice) * 20, 0) / 20
from (select sale_id, round(sum(price) * 20, 0 ) / 20 as tprice
from item
group by sale_id);

Million thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-22 : 09:32:58

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources