I am writing a custom report using SQL syntax, and all is going quite well with the exception of where I am trying to count the # of physical hard drives and return a total...
There seems to be a built in floppy drive count, but for reasons beyond comprehension a physical hard disk count was left out.
In any case, I can't seem to get the syntax right.
Everything is done using SELECT DISTINCT and Unions so its quite difficult inserting some count commands..I must have tired 10 different methods for this but I am hitting a brick wall.
Any insight would be very helpful!
Here is the code:
--Hard Drive Count SELECT DISTINCT Computer.Computer_Idn, Computer.DeviceName, 5, DriveNo, null, 0, N'Total # of Hard Drives', DriveNo, null FROM Computer LEFT OUTER JOIN FixedDrives ON Computer.Computer_Idn = FixedDrives.Computer_Idn UNION ALL --End Hard Drive Count
I can't just toss in a Count (DriveNo) as it complains about Computer_IDN not being included.
I can't seem to wrap the sql inside a nested select for counting either as it then complains about columsn not being given names, etc.