WinSAT scores and an average of that score

[Tholmes]

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

[RCorbeil]

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]

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]

As always, thanks for the awesome solution.
Thank you.
Tim

[RCorbeil · Accepted solution]

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]

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]

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