CREATE Calculation in VIEWS Please help

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)

CREATE Calculation in VIEWS Please help

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2006-04-21 : 09:33:41

Hello,

Here is my view and data. I am trying to list the EMP_ID and calculate the total of inv_hours, group by invID, using COLD Fusion. I want to be able to list each EMP_ID then have another row to calculate inv_hours. Do you know how to create a view like this. I want to have another column to calculate the inv_hours. Ex: for invID = 100, it lists 3 rows, then the last column show the total inv_hours.
Is there any way to do this? or have to do the total in cold fusion?

SELECT invoice_id, EMP_ID, Invoice_Hours
from Invoice
invID EMP_ID Inv_hours
100 1 10
100 2 12
100 3 20
200 1 8
200 2 9

Thanks,
Jenny.

There is no stupid question.
www.single123.com

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 10:02:58

welcome back prettyjenny...

select invid,sum(inv_hours)
from invoice
group by invid

do the totals in your apps

--------------------
keeping it simple...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-21 : 10:07:52

I don't know whether the following (taken from BOL & modified) helps u

USE pubs
GO
SELECT Type, Title, Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE Sum(price) BY type
GO

or U may do it in the front end

Its easier to understand if u give us how u expect, the results to be (for the sample data that u have given)

Srinika

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2006-04-21 : 10:09:37

Thanks. I'll try that.

There is no stupid question.
www.single123.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 10:40:20

>>welcome back prettyjenny...

Is she your friend?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 11:00:47

everybody's a friend here...

but i like jenny's username, sounds pretty and cute

noh?

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 11:05:21

quote:
Originally posted by jen

everybody's a friend here...

but i like jenny's username, sounds pretty and cute noh?

--------------------
keeping it simple...

Yes it is

select soundex('jenny'),soundex('jen')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources