Adding Row Values when there are no results - MySQL -

problem statement: need result set include records not naturally return because null.

i'm going put simplified code here since code seems long.

table scores has company_type, company, score, project_id

select score, count(project_id)     scores     company_type= :company_type     group score 

results in following:

score   projects 5       95 4       94 3       215 2       51 1       155 

everything working fine until apply condition company_type not include results in 1 of 5 score categories. when happens, don't have 5 rows in result set more.

it displays this:

score   projects 5       5 3       6 1       3 

i'd display this:

score   projects 5       5 4       0 3       6 2       0 1       3 

i need results display 5 rows. (scores = 1-5)

---

i tried 1 of approaches below spencer7593. simplified query looks this:

select i.score score, ifnull(count(*), 0) projects (select 5 score union select 4 union select 3 union select 2 union select 1) left join scores on scores.score = i.score group score order i.score desc

and gives following results, accurate except rows 1 in projects should 0 because derived "i". there no projects score of 5 or 2.

score   projects 5       1 4       5 3       6 2       1 1       3 

---

solved! needed adjust count @ project count - count(project) rather count(*). returned expected results.

if want query return 5 rows, score values of 5,4,3,2,1... you'll need rowsource supplies score values.

one approach use simple query return fixed values, e.g.

 select 5 score  union select 4  union select 3  union select 2  union select 1 

then use query inline view, , outer join operation results current query

 select i.score                `score`       , ifnull(q.projects,0)   `projects`    ( select 5 score           union select 4           union select 3           union select 2           union select 1         )    left    join (           -- current query "missing" score rows goes here           -- completeness of example, without query            -- emulate result different query           select 5 score, 95 projects           union select 3, 215           union select 1, 155         ) q      on q.score = i.score   order i.score desc 

it doesn't have view query in example. there need rowsource rows can returned from. could, example, have simple table contains 5 rows, 5 score values.

this example approach general approach. might possible modify existing query return rows want. without seeing query, schema, , example data, can't tell.

---

followup

based on edit question, showing example of current query.

if guaranteed 5 values of score appear in scores table, conditional aggregation, writing query this:

 select s.score       , count(if(s.company_type = :company_type,s.project_id,null)) projects   scores s  group s.score  order s.score desc 

note require scan of rows, may not perform well. "trick" if function, returns null value in place of project_id, when row have been excluded clause.)

if guaranteed project_id non-null, use more terse mysql shorthand expression achieve equivalent result...

      , ifnull(sum(s.company_type = :company_type),0) projects 

this works because mysql returns 1 when comparison true, , otherwisee returns 0 or null.