cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
Here is my code so far, I have modified someone else's query to match what I need, I just need an average of the scores, how would I go about this please

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
Convert(Decimal(5,2),Sum(tblWinSAT.WinSPRLevel)) As [Base score],
Convert(Decimal(5,2),Sum(tblWinSAT.CPUScore)) As Processor,
Convert(Decimal(5,2),Sum(tblWinSAT.MemoryScore)) As [Memory (RAM)],
Convert(Decimal(5,2),Sum(tblWinSAT.GraphicsScore)) As Graphics,
Convert(Decimal(5,2),Sum(tblWinSAT.D3DScore)) As [Gaming graphics],
Convert(Decimal(5,2),Sum(tblWinSAT.DiskScore)) As [Primary hard disk]
From tblWinSAT
Inner Join tblAssets On tblAssets.AssetID = tblWinSAT.AssetId
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress
Having tblAssets.AssetID Is Not Null And Len(RTrim(LTrim(tblAssets.AssetID))) >
0

Once again, thanks for any and all comments and contributions.
Tim
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
So just an average of the six scores per asset?
  Convert(Decimal(5,2), ( Sum(tblWinSAT.WinSPRLevel) +
Sum(tblWinSAT.CPUScore) +
Sum(tblWinSAT.MemoryScore) +
Sum(tblWinSAT.GraphicsScore) +
Sum(tblWinSAT.D3DScore) +
Sum(tblWinSAT.DiskScore)
) / 6
) AS [Average score]

View solution in original post

6 REPLIES 6
RCorbeil
Honored Sweeper II
I hadn't looked at tblWinSAT before but I just did. You don't need to do the SUM()s that you have in your query.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblWinSAT.WinSPRLevel As [Base score],
tblWinSAT.CPUScore As Processor,
tblWinSAT.MemoryScore As [Memory (RAM)],
tblWinSAT.GraphicsScore As Graphics,
tblWinSAT.D3DScore As [Gaming graphics],
tblWinSAT.DiskScore As [Primary hard disk],
Convert(Decimal(5,2), ( tblWinSAT.WinSPRLevel +
tblWinSAT.CPUScore +
tblWinSAT.MemoryScore +
tblWinSAT.GraphicsScore +
tblWinSAT.D3DScore +
tblWinSAT.DiskScore
) / 6
) AS [Average score]
From
tblWinSAT
Inner Join tblAssets On tblAssets.AssetID = tblWinSAT.AssetId
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And tblADusers.Userdomain = tblAssets.Userdomain
Where
tblAssets.AssetID Is Not Null
And Len(RTrim(LTrim(tblAssets.AssetID))) > 0
Tholmes
Engaged Sweeper III
Thank you so much, really helped and made it look a little better
Regards
Tim

RC62N wrote:
I hadn't looked at tblWinSAT before but I just did. You don't need to do the SUM()s that you have in your query.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblWinSAT.WinSPRLevel As [Base score],
tblWinSAT.CPUScore As Processor,
tblWinSAT.MemoryScore As [Memory (RAM)],
tblWinSAT.GraphicsScore As Graphics,
tblWinSAT.D3DScore As [Gaming graphics],
tblWinSAT.DiskScore As [Primary hard disk],
Convert(Decimal(5,2), ( tblWinSAT.WinSPRLevel +
tblWinSAT.CPUScore +
tblWinSAT.MemoryScore +
tblWinSAT.GraphicsScore +
tblWinSAT.D3DScore +
tblWinSAT.DiskScore
) / 6
) AS [Average score]
From
tblWinSAT
Inner Join tblAssets On tblAssets.AssetID = tblWinSAT.AssetId
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And tblADusers.Userdomain = tblAssets.Userdomain
Where
tblAssets.AssetID Is Not Null
And Len(RTrim(LTrim(tblAssets.AssetID))) > 0


Tholmes
Engaged Sweeper III
As always, thanks for the awesome solution.
Thank you.
Tim
RCorbeil
Honored Sweeper II
So just an average of the six scores per asset?
  Convert(Decimal(5,2), ( Sum(tblWinSAT.WinSPRLevel) +
Sum(tblWinSAT.CPUScore) +
Sum(tblWinSAT.MemoryScore) +
Sum(tblWinSAT.GraphicsScore) +
Sum(tblWinSAT.D3DScore) +
Sum(tblWinSAT.DiskScore)
) / 6
) AS [Average score]
RCorbeil
Honored Sweeper II
Select Top 1000000
Convert(Decimal(5,2), Avg(tblWinSAT.WinSPRLevel)) As [Base score],
Convert(Decimal(5,2), Avg(tblWinSAT.CPUScore)) As Processor,
Convert(Decimal(5,2), Avg(tblWinSAT.MemoryScore)) As [Memory (RAM)],
Convert(Decimal(5,2), Avg(tblWinSAT.GraphicsScore)) As Graphics,
Convert(Decimal(5,2), Avg(tblWinSAT.D3DScore)) As [Gaming graphics],
Convert(Decimal(5,2), Avg(tblWinSAT.DiskScore)) As [Primary hard disk]
From
tblWinSAT
Inner Join tblAssets On tblAssets.AssetID = tblWinSAT.AssetId
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And tblADusers.Userdomain = tblAssets.Userdomain
Where
tblAssets.AssetID Is Not Null And Len(RTrim(LTrim(tblAssets.AssetID))) > 0
Tholmes
Engaged Sweeper III
Sorry, I don't think I explained myself very well.
I am after all the users and an average of their scores.
The below report just gives me a total average of the scores.
Thanks
Tim


RC62N wrote:
Select Top 1000000
Convert(Decimal(5,2), Avg(tblWinSAT.WinSPRLevel)) As [Base score],
Convert(Decimal(5,2), Avg(tblWinSAT.CPUScore)) As Processor,
Convert(Decimal(5,2), Avg(tblWinSAT.MemoryScore)) As [Memory (RAM)],
Convert(Decimal(5,2), Avg(tblWinSAT.GraphicsScore)) As Graphics,
Convert(Decimal(5,2), Avg(tblWinSAT.D3DScore)) As [Gaming graphics],
Convert(Decimal(5,2), Avg(tblWinSAT.DiskScore)) As [Primary hard disk]
From
tblWinSAT
Inner Join tblAssets On tblAssets.AssetID = tblWinSAT.AssetId
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And tblADusers.Userdomain = tblAssets.Userdomain
Where
tblAssets.AssetID Is Not Null And Len(RTrim(LTrim(tblAssets.AssetID))) > 0


New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now