July 13, 2011 at 6:52 am
Hi
I have a Year field that contains, 2009,2010,2011 etc.
How can I write a formula that will count the number of different Assets (which is another field) without doing a matrix?
Thanks
Carl.
July 13, 2011 at 7:04 am
This was removed by the editor as SPAM
July 13, 2011 at 7:07 am
I have Year Field, Asset Field, and Count(Asset) as data, Ie:
Year
2009 2010
Asset
A 12 14
B 5 3
And I want the vaiance by asset for each year
Regards
Carl.
July 13, 2011 at 7:12 am
From the information that is given , I believe you are trying COUNT the assets based on the YEAR groups in which it is.
The general solution for this problem will be a select statement similiar to :-
SELECT COUNT(ASSETS)
FROM <YourTableName>
GROUP BY year
as stated earlier, provide more details for a clearer solution
July 13, 2011 at 7:14 am
Not quite, I want a variance in a matrix type report for 2009 vs 2010 by asset!
July 13, 2011 at 7:19 am
--create test data
create table test_count (asset_year int, asset varchar(10));
go
insert into test_count values (2009, 'A')
go 12
insert into test_count values (2010, 'A');
go 14
insert into test_count values (2009, 'B');
go 5
insert into test_count values (2010, 'B');
go 3
--verify data
select * from test_count
--select
select asset_year, asset, COUNT(asset)
from test_count
group by asset_year, asset
--clean up
drop table test_count
edit: added "go" after create table
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 13, 2011 at 7:36 am
Or if you want the results in a table format, you could try this:
select asset_year,
sum(case when asset = 'A' then 1 else 0 end) A,
sum(case when asset = 'B' then 1 else 0 end) B
from test_count
group by asset_year
Using my test setup of course.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply