Union Problem.

[macubergeek]

Here is my original query:
USE nessus;
SELECT count(risk) AS "Serious Risk:"
FROM results
WHERE risk = 1;
SELECT count(risk) AS "High Risk:"
FROM results
WHERE risk = 2;
SELECT count(risk) AS "Medium Risk:"
FROM results
WHERE risk = 3;
SELECT count(risk) AS "Medium Low Risk:"
FROM results
WHERE risk = 4;
SELECT count(risk) AS "Low Medium Risk:"
FROM results
WHERE risk = 5;
SELECT count(risk) AS "Low Risk:"
FROM results
WHERE risk = 6;
SELECT count(risk) AS TOTAL
FROM results;

1. Now the problem is this returns a result like this for each query:
mysql> SELECT count(risk) AS "Serious Risk:"
-> FROM results
-> WHERE risk = 1;
+---------------+
| Serious Risk: |
+---------------+
| 0 |
+---------------+
1 row in set (0.01 sec)

2. A union query:
USE nessus;
(SELECT count(risk) AS "Serious Risk:" FROM results WHERE risk = 1)
UNION
(SELECT count(risk) AS "High Risk:" FROM results WHERE risk = 2)
UNION
(SELECT count(risk) AS "Medium Risk:" FROM results WHERE risk = 3)
UNION
(SELECT count(risk) AS "Medium Low Risk:" FROM results WHERE risk = 4)
UNION
(SELECT count(risk) AS "Low Medium Risk:" FROM results WHERE risk = 5)
UNION
(SELECT count(risk) AS "Low Risk:" FROM results WHERE risk = 6)

returns a result like this:
+---------------+
| Serious Risk: |
+---------------+
| 0 |
| 36 |
| 304 |
| 866 |
+---------------+
4 rows in set (0.41 sec)

How do I display the seperate query outputs in 1. above side by side instead of sequentially? Am I barking up the wrong tree with a Union query here?

 

[azzazzello]

How bout this...Create a table called risklabel with an ID field matching nessus.risk entries. In your case it would be

table risklabel

riskID label
1 | Serious Risk
2 | High Risk
3 | Medium Risk
....

Then you do this.

SELECT label, count(risk) as occurances FROM risklabel, nessus WHERE riskID = risk GROUP BY risk

GROUP BY will take care of the count, and label table will give you the appropriate names

 

[macubergeek]

I created the table per your suggestion but this is what I get:
mysql> use nessus;
Database changed
mysql> SELECT label, count(risk) as occurances FROM risklabel, nessus WHERE risk
ID = risk GROUP BY risk;
ERROR 1146 (42S02): Table 'nessus.nessus' doesn't exist
mysql>

 

[azzazzello]

OOPS! sorry, I mistyped a bit you should be selecting from results (your table name) not nessus (database). So...

SELECT label, count(risk) as occurances FROM risklabel, results WHERE riskID = risk GROUP BY risk

 

[macubergeek]

Oh yeh that worked!
Thanks!!!
mysql> SELECT label, count(risk) as occurances FROM risklabel, results WHERE ris
kID = risk GROUP BY risk;
+-------------+------------+
| label | occurances |
+-------------+------------+
| High Risk | 36 |
| Medium Risk | 304 |
| Low Risk | 866 |
+-------------+------------+
3 rows in set (0.03 sec)