0
| proj_id | list_date  | state  | Status     | 
|   1     |   03/05/10 |   CA   | Finished   |
|   2     |   04/05/10 |   WA   | Unfinished |
|   3     |   03/05/10 |   WA   | Finished   |
|   4     |   04/05/10 |   CA   | Finished   |
|   5     |   03/05/10 |   WA   | Unfinished |
|   6     |   04/05/10 |   CA   | Finished   |

What query can I write so to determine the percentage of projects in each listing month and state are finished?

I currently have written the following code to group the amount of projects by its month and state

select 
    month(list_date), state_name, count (*) as Projects 
from 
    projects 
group by 
    month(list_date), state_name;

How do I then incorporate the percentage of projects in each listing month and state that are finished or unfinished?

Desired output:

     | list_date | state  | Proj_Count | % Finished
     |   March   |   CA   |     2      |    10%    
     |   April   |   WA   |     3      |    20%
     |   July    |   WA   |     6      |    50%
     |   August  |   CA   |     5      |    40%
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JuniorSQLearner
  • 65
  • 2
  • 6
  • Possible duplicate of [How to calculate percentage with a SQL statement](https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement) – Wayne Phipps Nov 30 '18 at 08:29
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Nov 30 '18 at 09:01
  • I’m using MySql – JuniorSQLearner Nov 30 '18 at 15:25

2 Answers2

1

Put this in the select:

100 * count(case when state = 'Finished' then 1 else null end) / cast(count(*) as float)

I wasn't clear if you want the percentage finished by state and month, or just by month. If the latter it's a bit more involved

Count counts the non nulls in a set. If the state is finished, 1 is returned. If the state is anything else, null is returned and it doesn't contribute to the count

Note that this calc above will be done using INT so we cast one of the operands to a float (or whatever decimal point supporting type your database has) so that the calc is done using decimal places instead. Without this some db would simply produce 0 because small_int/large_int is always 0

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
1

You can easily do this with the avg() aggregation function:

select month(list_date), state_name, count(*) as Projects,
       avg(case when status = 'Finished' then 100.0 else 0 end) as percent_finished 
from projects 
group by month(list_date), state_name;

To see how this works, just work it out on an example:

Finished        100
Unfinished        0
Finished        100
Unfinished        0
Unfinished        0

The average is (100 + 100) / 5 = 40. That is the percentage you are looking for.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Quick question - do you mind explaining the rationale/action of the code mentioned above? This would help me a lot to understand. I'm having a hard time understanding the avg (case when status = 'finished' then 100.0 else 0 and how that results in percent_finished. – JuniorSQLearner Dec 01 '18 at 04:39