views - 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

General SQL Server Forums

New to SQL Server Programming

views

Author

Topic

orls
Starting Member

2 Posts

Posted - 2009-03-16 : 17:15:41

Hey

I need some help! I must create a view that groups each ExpDesc (Expense Descriptions) and the average Cost (ExpAmt)
Here is my table:

Create table ExpenseItem
(
EINo integer not null,
ExpDesc varchar (255) not null,
ExpenseDate datetime default getdate(),
ExpAmt money default 0 not null,
ExpApprAmt money default 0,
ERNo integer not null,
ECNo integer not null,
AssetNo integer null,
PRIMARY KEY (EINo),
FOREIGN KEY (AssetNo) REFERENCES Asset,
FOREIGN KEY (ECNo) REFERENCES ExpCat,
Foreign KEY (ERNo) REFERENCES ExpenseReport (ERNo)
);

any help much appreciated

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:20:39

[code]select ExpDesc , sum(ExpAmt) from ExpenseItem group by ExpDesc [/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-16 : 17:21:48

homework? What have you tried so far?

I think he needs average sakets

Be One with the Optimizer
TG

orls
Starting Member

2 Posts

Posted - 2009-03-16 : 17:24:33

I have tried:

CREATE VIEW ExpenseItem_View1 AS
SELECT ExpDesc, avg(ExpAmt)
FROM ExpenseItem
GROUP BY ExpDesc

But I get this error:
Msg 4511, Level 16, State 1, Procedure ExpenseItem_View1, Line 2
Create View or Function failed because no column name was specified for column 2.

any ideas?!?!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:26:39

quote:
Originally posted by TG

homework? What have you tried so far?

I think he needs average sakets

Be One with the Optimizer
TG

Woops,, Didn't see that.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:28:55

quote:
Originally posted by orls

I have tried:

CREATE VIEW ExpenseItem_View1 AS
SELECT ExpDesc, avg(ExpAmt)
FROM ExpenseItem
GROUP BY ExpDesc

But I get this error:
Msg 4511, Level 16, State 1, Procedure ExpenseItem_View1, Line 2
Create View or Function failed because no column name was specified for column 2.

any ideas?!?!

Nice.. Just assign column 'avg(ExpAmt)' a name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:44:17

the error message is so obvious. Couldn't you still sort it out?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources