After searching the forums for this query and coming up short, I thought I would post my solution in case it helps someone else. The very simple SQL query below (I'm definitely no expert) will output a list of host names and display the sum of each machine's critical, severe, and moderate vulnerabilities for a given scope. The scope could be site(s)/scan(s) depending on the scope given within the reporting setup.
SELECT da.host_name, fa.critical_vulnerabilities, fa.severe_vulnerabilities, fa.moderate_vulnerabilities FROM fact_asset fa, dim_asset da WHERE fa.asset_id = da.asset_id;
Please let me know if this does or does not work for you, again I'm no SQL expert and appreciate the feedback.