I’ve the following SQL Query which runs perfectly fine but now i want to calculate the count based on the following scenario:
x
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
Advertisement
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