require('dotenv').load();
var pg = require('pg');
var conString = "postgres://" + process.env.DB_USER + ":" + process.env.DB_PASS +"@donors-choose.ct8ks5pp86im.us-west-2.redshift.amazonaws.com:5439/donorschoose";

exports.handler = function(event, context) {

    pg.connect(conString, function(err, client, done) {
        if(err) {
            context.fail(err);
            return console.error('error fetching client from pool', err);
        }
        client.query("SELECT UPPER(school_state) AS state, COUNT(1) AS total_projects FROM donorschoose_projects GROUP BY state", function(err, result) {
            done();

            if(err) {
                return console.error('error running query', err);
            }
            var results = result.rows.reduce(function(total, row) {
                total[row.state] = parseInt(row.total_projects);
                return total;
            }, {});
            console.log(results);
            context.succeed(results);
        });
    });
};
@@ -454,21 +454,30 @@
     
-client.q
+var stateGroupQ
 uery
-(
+ = 
 %22SEL
@@ -514,15 +514,27 @@
 te, 
-COUNT(1
+SUM(total_donations
 ) AS
@@ -539,25 +539,16 @@
 AS total
-_projects
  FROM do
@@ -582,16 +582,54 @@
 Y state%22
+;%0A        client.query(stateGroupQuery
 , functi
@@ -636,17 +636,22 @@
 on(err, 
-r
+groupR
 esult) %7B
@@ -655,29 +655,341 @@
 ) %7B%0A
-            done();%0A%0A
+%0A%0A            if(err) %7B%0A                return console.error('error running query', err);%0A            %7D%0A            var devAvgQuery = %22SELECT AVG(s.total) AS average, STDDEV(s.total) AS standard_deviation FROM(%22 + stateGroupQuery +%22) s%22%0A            client.query(devAvgQuery, function(err, devAvgResult) %7B%0A                done();%0A    
     
@@ -998,32 +998,36 @@
       if(err) %7B%0A
+    
                 
@@ -1080,34 +1080,42 @@
 r);%0A            
-%7D%0A
+    %7D%0A    
             var 
@@ -1118,125 +1118,496 @@
 var 
-results = result.rows.reduce(function(total, row) %7B%0A                total%5Brow.state%5D = parseInt(row.total_projects);%0A
+average = devAvgResult.rows%5B0%5D.average;%0A                var standardDeviation = devAvgResult.rows%5B0%5D.standard_deviation;%0A%0A                var results = groupResult.rows.reduce(function(total, row) %7B%0A                    var totalDonations = parseInt(row.total);%0A                    var devDiff = devDiffResult(totalDonations);%0A                    total%5Brow.state%5D = %7B%0A                        %22total%22 : totalDonations,%0A                        %22devDiff%22 : devDiff%0A                    %7D;%0A    
     
@@ -1648,16 +1648,20 @@
     
+    
 %7D, %7B%7D);%0A
     
@@ -1652,24 +1652,178 @@
     %7D, %7B%7D);%0A
+                function devDiffResult(totalDonations) %7B%0A                    return (totalDonations - average) / standardDeviation;%0A                %7D%0A    
             
@@ -1836,32 +1836,36 @@
 e.log(results);%0A
+    
             cont
@@ -1878,32 +1878,49 @@
 cceed(results);%0A
+            %7D);%0A%0A
         %7D);%0A    

add deviation difference of donations by state