June 22, 2005 at 2:55 pm
I've created a view as follows:
CREATE VIEW opvw_actPerMile AS
SELECT op_m.mile_code,
COUNT(ac_a.acid) [CompAct]
FROM ac_activity ac_a INNER JOIN op_milestones op_m ON ac_a.mileid = op_m.mileid
WHERE ac_a.result_code = 'Completed' AND op_m.mile_status = 'Completed'
GROUP BY op_m.mile_code,
op_m.mileid
ORDER BY op_m.mile_code
Here is a sample of what is in that view:
Satisfaction 2
Invoice 1
Satisfaction 3
Satisfaction 3
Satisfaction 2
Buy In 2
Scope 1
I then want to get the average per mile_code so I've writen the following select statement:
SELECT mile_code,
AVG(CompAct)
FROM opvw_actPerMile
GROUP BY mile_code
ORDER BY mile_code
This yields:
mile_code Column1
Buy In 2
Deployment 2
Investigation Report 13
Invoice 1
Order 1
Proposal 1
Satisfaction 1
Scope 1
How can I get it so that I have decimal places included in the average?
In addition, if there is a way I can do this without first creating a view that would be great as well.
June 22, 2005 at 3:00 pm
SELECT mile_code,
AVG(Cast(CompAct as decimal(18,2)))
FROM opvw_actPerMile
GROUP BY mile_code
ORDER BY mile_code
June 22, 2005 at 3:02 pm
OR
SELECT mile_code,
AVG(CompAct *1.0)
FROM opvw_actPerMile
GROUP BY mile_code
ORDER BY mile_code
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply