cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
blighter
Engaged Sweeper III
I'm trying to create a report of machines grouped by username and it keeps coming up with the same error:

In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = tblAssets,AssetID ]

This is the SQL:

Select Top 1000000 tblADusers.Displayname As Store,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblOperatingsystem.Caption,
tblOperatingsystem.ServicePackMajorVersion,
tblAssets.Memory,
tblAssets.Processor,
tblProcessor.NumberOfCores,
tblProcessor.MaxClockSpeed,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblADComputers.OU Like '%EU99-Stores%' And tsysAssetTypes.AssetTypename =
'Windows' And tblAssetCustom.State = 1
Group By tblADusers.Displayname


Any ideas? 😞
1 REPLY 1
Hemoco
Lansweeper Alumni
Could you clarify what you are trying to do. Grouping is used for counting things, finding max values etc. Are you trying to sort by Displayname? If so, you need to replace Group By tblADusers.Displayname with Order By tblADusers.Displayname. Also note that you can sort directly from within report results.