SQL Count to include null values - workaround?

Hi Greg,

Can you provide more information about your query? I'm interested on the field types of primary_competitor and win_loss.

Also, are you planning to use this query to generate a report? or do you simply want to get the win rate of each competitor?


Thanks,
Godfrey

Thanks, Godfrey. Both field types are picklists. I am using the queries to generate a string of the form "Primary Competitor A, 4, 8, Primary Competitor B, 6, 9" which I then pass to client-side Javascript to split into an array and use the values to generate a custom chart. My code for the formula field for generating the string is this:

function main() {

	var winsPrimaryCompetitorArray = rbv_api.selectQuery("SELECT primary_competitor#value,COUNT(primary_competitor) FROM opportunities WHERE win_loss#value='Win' GROUP BY primary_competitor#value", 1000);
	var countPrimaryCompetitor = rbv_api.selectQuery("SELECT primary_competitor#value,COUNT(primary_competitor) FROM opportunities GROUP BY primary_competitor#value", 1000);

	var winsPrimaryCompetitorString = "";

	for(var i=0; i<winsPrimaryCompetitorArray.length;i++){
		for (var j=0; j<2;j++) {
			winsPrimaryCompetitorString += winsPrimaryCompetitorArray[i][j] + ", ";
			if (i == winsPrimaryCompetitorArray.length-1 && j == 1){
				winsPrimaryCompetitorString += countPrimaryCompetitor[i][1];
				break;
			}
			if (i != winsPrimaryCompetitorArray.length-1 && j == 1) {
				winsPrimaryCompetitorString += countPrimaryCompetitor[i][1] + ", ";
            }
		}
	}

	return winsPrimaryCompetitorString;

	}

main();

By splitting the string I then get the chart labels and win rates for the competitors.

Let me know if any further information would be helpful.

Thanks,

Greg

I was not able to filter records using win_loss#value='Win' in the WHERE clause so I used the picklist ID instead. However, it is not a good practice to use IDs in picklists so I also used getIdByCode to make sure the code will not break when exporting to other zones.

winId = rbv_api.getIdByCode("object_a3", "Win_or_Lose", 'win');
winsArray = rbv_api.selectQuery("SELECT Competitor, count(1) FROM object_a3 WHERE Win_or_Lose=? GROUP BY Competitor#value", 1000, winId);

For the query, simply use the countPrimaryCompetitor to list down all the competitors. Then use the winsPrimaryCompetitorArray to get the winning rate. If there is no entry for a competitor in winsPrimaryCompetitorArray that would only it had never won. Tweaking a few lines in your code should solve the problem. 

totalArray = rbv_api.selectQuery("SELECT Competitor, count(1) FROM object_a3 GROUP BY Competitor#value", 1000);

var winsPrimaryCompetitorString = "";

for (counterTotal=0; counterTotal < totalArray.length; counterTotal++){
  for (counterWins=0; counterWins < winsArray.length; counterWins++){
    if (totalArray[counterTotal][0] == winsArray[counterWins][0]) { 
      winsPrimaryCompetitorString += totalArray[counterTotal][0] +","+ winsArray[counterWins][1] +","+ totalArray[counterTotal][1];
      break;
    } else if (counterWins == winsArray.length-1){
      winsPrimaryCompetitorString += totalArray[counterTotal][0] +","+ 0 +","+ totalArray[counterTotal][1];
      break;
    }
  }
}
return winsPrimaryCompetitorString;

Thank you!