I am not a SQL person, but try this below
SELECT
volume.name as Name,
volume.size_mb as 'Total Size (MB)',
volume.available_size_mb as 'Available Size (MB)',
count (lun.id WHERE lun.volume_id = volume.id ) AS luncount
FROM
cm_storage.vserver,
cm_storage.cluster,
cm_storage.volume,
cm_storage.lun
WHERE
volume.vserver_id = vserver.id
AND vserver.cluster_id = cluster.id
AND vserver.name = '${SVMNameDCA}'
AND cluster.name = '${ClusterNameDCA}'
AND volume.name NOT LIKE '%root%'
ORDER BY
volume.available_size_mb DESC
Mike