Working on a SugarCRM dashboard today, and I needed to count how many records had one value and how many records had a second value. I then wanted to know what percentage the first value was of the total. So there’s a bit of MySQL code that helps make this easy to do.
SELECT SUM(IF(field_name = 'value_one',1,0)) AS 'Value One', SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two' FROM table_name
What this does is counts up how many times
value_two appear in
field_name in the table
You can then take this a step further, if you wanted to, and I did…
you can use a line like this within your query:
This gives you the percentage that
value_one is of (
Hat tip to this stackoverflow answer by eisberg.
Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.