Karen G Karen G - 5 months ago 9
MySQL Question

Adding Row Values when there are no results - MySQL

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.

Table

Scores
has
Company_type
,
Company
,
Score
,
Project_ID


Select Score, Count(Project_ID)
FROM Scores
WHERE company_type= :company_type
GROUP BY Score


Results in the following:

Score Projects
5 95
4 94
3 215
2 51
1 155


Everything is working fine until I apply a condition to company_type that does not include results in one of the 5 score categories. When this happens, I don't have 5 rows in my result set any more.

It displays like this:

Score Projects
5 5
3 6
1 3


I'd like it to display like this:

Score Projects
5 5
4 0
3 6
2 0
1 3


I need the results to always display 5 rows. (Scores = 1-5)




I tried one of the approaches below by Spencer7593. My simplified query now looks like this:

SELECT i.score AS
Score
, IFNULL(count(*), 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 Scores ON Scores.score = i.score
GROUP BY Score
ORDER BY i.score DESC

And gives the following results, which is accurate except that the rows with 1 in Projects should actually be 0 because they are derived by the "i". There are no projects with a score of 5 or 2.

Score Projects
5 1
4 5
3 6
2 1
1 3





Solved! I just needed to adjust my count to specifically look at the project count - count(project) rather than count(*). This returned the expected results.

Answer

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 Score values.

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.


FOLLOWUP

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.

Comments