Combine like lines based off of date

Question

I'm having trouble finishing this query. The end result I'm looking for is:

I have so far wrote this query:

SELECT
to_char(favi.date::date, 'mm-dd-yyyy') AS "Date",
(SELECT COUNT(dv.severity) WHERE dv.severity='Critical') AS "Critical Vulns",
(SELECT COUNT(dv.severity) WHERE dv.severity='Severe') AS "Severe Vulns",
(SELECT COUNT(dv.severity) WHERE dv.severity='Moderate') AS "Moderate Vulns",
COUNT(favi.asset_id) AS "Asset Count"
FROM fact_asset_vulnerability_instance favi
JOIN dim_vulnerability dv ON dv.vulnerability_id=favi.vulnerability_id
GROUP BY to_char(favi.date::date, 'mm-dd-yyyy'), dv.severity
ORDER BY to_char(favi.date::date, 'mm-dd-yyyy')

My results though are:

How exactly do I combine the rows on the date? The results split the date into three rows each one displaying the actual count for whatever severity it is. I just want one row per date that shows each of the severity values on one line.

I've played around with UNION, DISTINCT, FULL JOIN, etc but this is slightly past my knowledge of how to get the desired results.

Answer 1

The below code is what ended up working.

SELECT
to_char(favi.date::date, 'mm-dd-yyyy') AS "Date",
SUM(CASE WHEN dv.severity = 'Critical' THEN 1 END) AS "Critical Vulns",
SUM(CASE WHEN dv.severity = 'Severe' THEN 1 END) AS "Severe Vulns",
SUM(CASE WHEN dv.severity = 'Moderate' THEN 1 END) AS "Moderate Vulns",
COUNT(favi.asset_id) AS "Asset Count"
FROM fact_asset_vulnerability_instance favi
JOIN dim_vulnerability dv ON dv.vulnerability_id=favi.vulnerability_id
GROUP BY to_char(favi.date::date, 'mm-dd-yyyy')
ORDER BY to_char(favi.date::date, 'mm-dd-yyyy')

so by doing the SUM function as @SOS mentioned I was able to drop the GROUP BY on dv.severity