Simple Aggregate - 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

Simple Aggregate

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-26 : 14:17:27

I've been looking at this way too long...lil'help please?

I want to produce a metric derived from counting the number of records that have certain field settings...

SELECT Count(Project_ID) as NumberClosed,getdate()as DateStored from Project WHERE ProjectState = 'Closed'
SELECT Count(Project_ID) as NumberCompleted,getdate()as DateStored from Project WHERE ProjectState = 'Completed'

I need to merge these two, and add (NumberClosed/NumberCompleted) as Metric4

Metric4 will be a percent rounded to the third decimal.

14/188 = .074468...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-26 : 14:52:46

This should work. The formatting should be done in the front end.
Jim


SELECT 
       [NumberClosed]    = SUM(CASE WHEN ProjectState= 'Closed'    THEN 1 ELSE 0 END)
      ,[NumberCompleted] = SUM(CASE WHEN ProjectState= 'Completed' THEN 1 ELSE 0 END)
      ,[Metric4]         = SUM(CASE WHEN ProjectState= 'Closed'    THEN 1 ELSE 0 END)*1.0
                          /SUM(CASE WHEN ProjectState= 'Completed' THEN 1 ELSE 0 END)
      ,[DateStored]      = getdate()
FROM
   project

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-26 : 14:59:06

like a champ!..thank you...

nuimberclosed numbercompleted metric4 datestored
14 188 .0744... 208-11-26...

^5

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-26 : 15:31:51

one more time...

I have ONE case where the metric goes against two tables...I expect I need to do some kind of multiple select in the from?..

Servicecosts.Projected where FY=08, summed, would give me the projected budget for fy09...
Spending.AvgIT where FY=09, summed, would give me the total expense for fy09 (the difference in the FY is that the budget for the next year is produced in the previous year, so they tag it with the FY in which the activity occurred (sigh))...

then the metric would be the Sum(Spending.AvgIT)/Sum([ServiceCosts.Projected)... or in my case 29/20 = 1.45 (or a 45% overrun)...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-26 : 17:29:50

There's not enuf info here to answer your questions. We'd need table structures from the tables involved and the relationship(s) between them. You should be able to join them together in one query.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 03:42:10

quote:
Originally posted by dlorenc

one more time...

I have ONE case where the metric goes against two tables...I expect I need to do some kind of multiple select in the from?..

Servicecosts.Projected where FY=08, summed, would give me the projected budget for fy09...
Spending.AvgIT where FY=09, summed, would give me the total expense for fy09 (the difference in the FY is that the budget for the next year is produced in the previous year, so they tag it with the FY in which the activity occurred (sigh))...

then the metric would be the Sum(Spending.AvgIT)/Sum([ServiceCosts.Projected)... or in my case 29/20 = 1.45 (or a 45% overrun)...

give details as per below mail and someone will be able to help

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-01 : 17:21:02

I have ONE case where the metric goes against two tables...I expect I need to do some kind of multiple select in the from?..

Table ServiceCosts contains budget information:
Record_ID; FY; AgencyCode; ServiceName; ProjectedCost
1;08;AGR;BatchServices;300000
2;08;AGR;TapeDriveService;100000
etc...
Servicecosts.ProjectedCosts where FY=08, summed, Grouped by AgencyCode would give me the projected budget for FY08.

Table Spending contains billing information:
Record_ID; FY; AgencyCode;AvgIT
1;08;AGR;305000
2;08;AGR;150000
etc...
Spending.AvgIT where FY=08, summed, grouped by AgencyCode would give me the total expense for fy08.

What I need to store then is
FY;Agencycode;BudgetToActual
where BudgetToActual is the Sum(Spending.AvgIT)/Sum(ServiceCosts.Projected)...

AGR =455000/400000 = 1.1375 (or a 13.75% overrun)...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:51:45

[code]SELECT t1.FY, t1.AgencyCode,
t2.TotalAvgIT*1.0/t1.TotalProjectedCost AS [BudgetToActual]
FROM
(
SELECT FY, AgencyCode,SUM(ProjectedCost) AS TotalProjectedCost
FROM ServiceCosts
GROUP BY FY, AgencyCode
)t1
INNER JOIN(SELECT FY, AgencyCode,SUM(AvgIT) AS TotalAvgIT
FROM Spending
GROUP BY FY, AgencyCode
)t2
ON t2.FY=t1.FY
AND t2.AgencyCode=t1.AgencyCode[/code]

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-02 : 14:25:03

that is exactly what I was looking to do..THANK YOU!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 23:28:29

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources