Grouping by negative and positive int/money

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

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping by negative and positive int/money

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-29 : 10:33:40

Brian writes "I've gone over a few things now trying to figure how i can group a set of records by being negative or positive.


--------------------
Sample Table
--------------------

Type Amount
---- ---------
4    5.00
4    10.95
4    -8.00
12   44.95
6    6.95
6    -6.95
6    -3.00

--------------------
End Result
--------------------

Type Amount Count
---- ------ -----
4    15.95  2
4    -8.00  1
12   44.95  1
6    6.95   1
6    -9.95  2

I've tried a few things though the end result is not what i'm looking for. Am I missing something?

Thanks,
Brian"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-29 : 10:58:37

Try this:

select type, sum(amount) as amt , County = count(type) from tSample
where amount >= 0
group by type
union
select type, sum(amount), count(type) from tSample
where amount < 0
group by type
order by type asc, amt desc

How do you want the result sorted?
What condition puts the type 12 record in the middle?

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 00:59:16

How About doing it in a single query

select Type,sum(amount) as Amount,Count(1) as Counts
from tSample
group by Sign(Amount),Type

HTH

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources