Subqueries and arithmetic? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Subqueries and arithmetic?

Author

Topic

tito_puente44
Starting Member

7 Posts

Posted - 2006-11-13 : 17:23:37

Hi all!

Wondering what best way of going about this is. Here's what I'm facing.

I have a table that holds transactions of sales of different items, with the itemID, agentID, date, type (of transaction, sale or buy) and units among other things.

Basically I need to run reports on each item, and see how agents are selling the item. I'm doing this all application side, but is very time consuming. I've been looking at ways to do it db side. I'm thinking it has to be a form of a sum function with subqueries to return the data set that I want.

I've figured out that if I run this query:

select sum(units) units, agentID
from trades
where itemID = '1'
group by agentID
order by agentID

I'll get a sum of all units for a specific item for various agents, but, it can't get figure out how to create the query to return an actual sum dependent on the type of the trade, buy - sell, etc...

anyone have any idea on how to go about creating queries with arithmetic?

thank you all

:)

tito_puente44
Starting Member

7 Posts

Posted - 2006-11-13 : 17:26:55

I've also been thinking about creating a view where i could sum all trades for buys in one query, and then through a union subtract the trades for sales, but i'm not sure how to code this...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 17:27:40

[code]select agentID, type, sum(units) units
from trades
where itemID = '1'
group by agentID, [b]type[b/]
order by agentID
[/code]

tito_puente44
Starting Member

7 Posts

Posted - 2006-11-13 : 17:32:48

khtan,

thanks for the quick reply!

that would give me a sum for types, and would create 2 rows for various agents, for each buy and sell. i guess the question is if there is a way to return a single result for each agent, where the result is buy sum subtracted by sell sum?

tito_puente44
Starting Member

7 Posts

Posted - 2006-11-15 : 13:20:07

Anyone? I know there isn't an easy answer for this task, and I don't expect anyone do to it for me, I'm just hoping someone could give me an insight into how it could be done.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 13:49:28

Read and learn more about PIVOT or CROSS TAB REPORTS.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 13:55:20

[code]select agentID,
sum(case when type = 'buy' then units else 0 end) buy,
sum(case when type = 'sell' then units else 0 end) sell
from trades
where itemID = '1'
group by agentID,
type
order by agentID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources