aggregate function help - 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 2005 Forums

Transact-SQL (2005)

aggregate function help

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-03-14 : 05:59:18

I have a table which has structure like this

sr_no
descr
asset_code
qty
purchase_amt
purchase_date
invoice_no
vendor
assigned_to
assign_date
approved_by

another table

asset_category
asset_code

I want to write a query which will show
asset_category || total_purchase_amt || total_purchase_amt_for_year2011
and this should be done grouped by asset_type.
for total purchase amount i want all addition of all purchase of that asset_code. however 3rd column, i.e purchase amount for 2011, i want totals for year 2011 only.
I could get for total purchase using simple group by, but how to show 3rd column in same table ????
can anyone please help ???

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-03-14 : 06:02:17

use having clause

shantanu88d
Starting Member

35 Posts

Posted - 2011-03-14 : 06:22:11

I tried to use this query

select category,
sum(purchase_amt) as Total,
(select sum(purchase_amt)
from purchased_assets
where datepart(yy,purchase_date)=2011
and asset_code = ???) as Total_of_2011
from asset_category, purchased_assets
group by category_code,asset_type, category
having asset_category.category_code = purchased_assets.asset_code

is there anyway that we can achieve asset_code dynamically.
or is there any other way? please suggest your query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources