Adding count to select - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding count to select

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-08-08 : 08:48:06

I have the following sql statement below and need to add a count to it but just can't seem to get the syntax right.

The count I need to add is this:

(Select count(*) from ProvisioningDeviceErrors Where ProvisioningDeviceErrors.deviceId = Device.DeviceID) as errorCnt

And the sql I need to add the above to:

SELECT DISTINCT Device.DeviceID
FROM Device
INNER JOIN ProvisioningDeviceErrors on ProvisioningDeviceErrors.deviceId = Device.DeviceID
LEFT JOIN DeviceSession ON Device.DeviceID = DeviceSession.DeviceID

WHERE (@operatingSystem IS NULL OR (ISNULL(Device.OSPlatform,'') = @operatingSystem))
AND (@oeminfo IS NULL OR (ISNULL(Device.OEMInfo,'') LIKE @oeminfo)) AND (@locationGroupID IN (SELECT LocationLink.LocationGroupID FROM LocationLink))));

So, when the final dataset returns, there is an additional field named errorCnt.

Suggestions?

Thanks,

Zath

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-08 : 08:55:56

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources