SQL Query Count With JOIN

I’ve the following SQL Query which runs perfectly fine but now i want to calculate the count based on the following scenario:

      SELECT d.vseverity, v.vulnstatus, v.vtitleid, d.vtitle 
    FROM vulnsummary v 
    JOIN project p ON v.projid = p.projid 
               AND v.stagename = p.currentstage 
    JOIN datasets d ON v.vtitleid = d.datasetid

The current Output is:

Now i want to show the count like this way:

High (Open) – 2

High (Closed) – 0

Medium (Open) – 1

Medium (Closed) – 0

Low (Open) – 3

Low (Closed) – 1

Please help me to solve this query, Thank You

Answer

You need to CROSS JOIN the distinct sets of severity and status values and then LEFT JOIN that to your table to allow you to count the values of each severity/status combination. Without sample data it’s hard to be certain but something like this should work:

SELECT sv.vseverity, st.vulnstatus, COUNT(v.vseverity) AS count
FROM (
    SELECT DISTINCT vseverity
    FROM datasets
) sv
CROSS JOIN (
    SELECT DISTINCT vulnstatus
    FROM vulnsummary
) st
LEFT JOIN (
    SELECT d.vseverity, v.vulnstatus 
    FROM vulnsummary v 
    JOIN project p ON v.projid = p.projid 
                  AND v.stagename = p.currentstage 
    JOIN datasets d ON v.vtitleid = d.datasetid
) v ON v.vseverity = sv.vseverity AND v.vulnstatus = st.vulnstatus
GROUP BY sv.vseverity, st.vulnstatus