Problem Statement: I need my result set to include records that would not naturally return because they are NULL.
I'm going to put some simplified code here since my code seems to be too long.
Select Score, Count(Project_ID)
WHERE company_type= :company_type
GROUP BY Score
If you always want your query to return 5 rows, with
Score values of 5,4,3,2,1... you'll need a rowsource that supplies those
One approach would be to use a simple query to return those fixed values, e.g.
SELECT 5 AS score UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1
Then use that query as inline view, and do an outer join operation to the results from your current query
SELECT i.score AS `Score` , IFNULL(q.projects,0) AS `Projects` FROM ( SELECT 5 AS score UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 ) i LEFT JOIN ( -- the current query with "missing" Score rows goes here -- for completeness of this example, without the query -- we emulate that result with a different query SELECT 5 AS score, 95 AS projects UNION ALL SELECT 3, 215 UNION ALL SELECT 1, 155 ) q ON q.score = i.score ORDER BY i.score DESC
It doesn't have to be the view query in this example. But there does need to be a rowsource that the rows can be returned from. You could, for example, have a simple table that contains those five rows, with those five score values.
This is just an example approach for the general approach. It might be possible to modify your existing query to return the rows you want. But without seeing the query, the schema, and example data, we can't tell.
Based on the edit to the question, showing an example of the current query.
If we are guaranteed that the five values of
Score will always appear in the
Scores table, we could do conditional aggregation, writing a query like this:
SELECT s.score , COUNT(IF(s.company_type = :company_type,s.project_id,NULL)) AS projects FROM Scores s GROUP BY s.score ORDER BY s.score DESC
Note that this will require a scan of all the rows, so it may not perform as well. The "trick" is the IF function, which returns a NULL value in place of project_id, when the row would have been excluded by the WHERE clause.)
If we are guaranteed that
project_id is non-NULL, we could use a more terse MySQL shorthand expression to achieve an equivalent result...
, IFNULL(SUM(s.company_type = :company_type),0) AS projects
This works because MySQL returns
1 when the comparison is TRUE, and otherwisee returns 0 or NULL.