sql server returns 0 in the output

Question

To getting the disk space details I am using the following query.

E.g.
SELECT distinct(volume_mount_point), 
 total_bytes/1048576 as Size_in_MB, 
 available_bytes/1048576 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY 
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
 available_bytes/1048576 order by 1

But the above query returns '0' in percentage column.How to fix this error?

Answer 1

Change some of the 1048576's to 1048576.0.

SELECT distinct(volume_mount_point), 
 total_bytes/1048576 as Size_in_MB, 
 available_bytes/1048576.0 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576.0) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576.0))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY 
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
 available_bytes/1048576.0 order by 1

It's an awful query though.

• SujithTee: your suggestion returns the following error.'Column 'sys.dm_os_volume_stats.total_bytes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'
• Cody Konior: It's due to the way you do the group by. See the code sample I've posted.