Talk:Stats and Testimonials - Revision history

Halfasspete: moved common SQL queries to own page

2006-01-29T22:53:30Z

moved common SQL queries to own page

Halfasspete: /* number of hours per volunteer, by year */

2006-01-29T22:42:33Z

number of hours per volunteer, by year

Halfasspete: /* number of hours per volunteer, by year */

2006-01-29T03:01:50Z

number of hours per volunteer, by year

Halfasspete: /* number of money donations, by year and amount */

2006-01-27T01:51:30Z

number of money donations, by year and amount

Halfasspete: /* SQL queries */

2006-01-26T21:43:34Z

SQL queries

Halfasspete at 21:29, 26 January 2006

2006-01-26T21:29:32Z

Halfasspete at 10:45, 25 January 2006

2006-01-25T10:45:03Z

Halfasspete: added some sql

2006-01-25T04:21:06Z

added some sql

Halfasspete at 12:57, 6 January 2006

2006-01-06T12:57:19Z

Halfasspete: /* SQL queries */

2006-01-04T20:18:22Z

SQL queries

← Older revision		Revision as of 22:53, 29 January 2006
Line 49:		Line 49:

	== SQL queries ==		== SQL queries ==
−	~~=== the basics ===~~
−	*psql fgdb -U fgdb (gets you in, once ssh'd to flowers)
−	*\dt (list tables)
−	*\d (describe a table)

−	~~=== number of money donations, by year and amount ===~~	+	See [[FGdb SQL queries]].
−	~~;first column of table on main page (Number of donations over $500, by year.)~~
−	~~:fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;~~
−
−	~~;second column (note alternate syntax for column names, and for range of values)~~
−	~~:SELECT EXTRACT( YEAR FROM created ) '''as Year''', COUNT(*) '''as DonCount''' FROM donation WHERE cashdonation + monitorfee '''between 400 and 499.99''' GROUP BY '''Year''' ORDER BY '''Year''';~~
−
−	~~=== number of hours per volunteer, by year ===~~
−	~~These~~ queries ~~show some of the pieces involved. Still working toward useful queries to use in real life.~~
−
−	~~;number of new adoption volunteers signed up, by year~~
−	~~:select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;~~
−
−	~~;how many hours Pete volunteered per year~~
−	~~:select extract( year from created), sum(hours) from memberhour where memberid=460 group by 1 order by 1;~~
−
−	~~;an attempt to modify the above query, to report only years where Martin logged more than 100 hours. DOES NOT WORK, needs some fixin.~~
−	~~:select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1;~~
−
−	~~;number of folks who have volunteered for us, ever~~
−	~~:select count( distinct memberid ) from memberhour;~~
−
−	~~;total number of hours logged for each volunteer in each year (yields a very long list!) I'm surprised by number of negative values. Need to look into that.~~
−	~~:select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;~~
−
−	~~;a subset of the above query's results, showing all volunteers who logged 80 hours or more in each year~~.
−	~~:select extract( year from created) as year, memberid, sum(hours) as hours from memberhour group by year, memberid having sum(hours) >= 80 order by year;~~

@@ Line 79: / Line 79: @@
 :select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;
-;'''strangeness'''
+;a subset of the above query's results, showing all volunteers who logged 80 hours or more in each year.
-:When I add the following (bolded) text to the above query, I get an unexpected result. I would expect this to yield a subset of the above query's results, showing all volunteers who logged over 80 hours in a given year. However, it yields a much smaller list. For instance, member 460 logged 84 hours in 2004, but is not listed.
+:select extract( year from created) as year, memberid, sum(hours) as hours from memberhour group by year, memberid having sum(hours) >= 80 order by year;

← Older revision		Revision as of 03:01, 29 January 2006
Line 77:		Line 77:

	;total number of hours logged for each volunteer in each year (yields a very long list!) I'm surprised by number of negative values. Need to look into that.		;total number of hours logged for each volunteer in each year (yields a very long list!) I'm surprised by number of negative values. Need to look into that.
−	:select extract( year from created ) as year, memberid, sum( hours ) from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;	+	:select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;
		+
		+	;'''strangeness'''
		+	:When I add the following (bolded) text to the above query, I get an unexpected result. I would expect this to yield a subset of the above query's results, showing all volunteers who logged over 80 hours in a given year. However, it yields a much smaller list. For instance, member 460 logged 84 hours in 2004, but is not listed.
		+	::select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour where memberid in (select distinct memberid from memberhour) '''and hours > 80''' group by year, memberid order by year;

@@ Line 59: / Line 59: @@
 ;second column (note alternate syntax for column names, and for range of values)
-:SELECT EXTRACT( YEAR FROM created ) '''as Year''', COUNT(*) '''as DonCount''' FROM donation WHERE cashdonation + monitorfee '''between 400 and 500''' GROUP BY '''Year''' ORDER BY '''Year''';
+:SELECT EXTRACT( YEAR FROM created ) '''as Year''', COUNT(*) '''as DonCount''' FROM donation WHERE cashdonation + monitorfee '''between 400 and 499.99''' GROUP BY '''Year''' ORDER BY '''Year''';
 === number of hours per volunteer, by year ===

@@ Line 59: / Line 59: @@
 ;second column (note alternate syntax for column names, and for range of values)
-:SELECT EXTRACT( YEAR FROM created ) as Year, COUNT(*) as DonCount FROM donation WHERE cashdonation + monitorfee between 400 and 500 GROUP BY Year ORDER BY Year;
+:SELECT EXTRACT( YEAR FROM created ) '''as Year''', COUNT(*) '''as DonCount''' FROM donation WHERE cashdonation + monitorfee '''between 400 and 500''' GROUP BY '''Year''' ORDER BY '''Year''';
 === number of hours per volunteer, by year ===
@@ Line 67: / Line 67: @@
 :select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;
-;how many hours Martin volunteered per year
+;how many hours Pete volunteered per year
-:select extract( year from created), sum(hours) from memberhour where memberid=5989 group by 1 order by 1;
+:select extract( year from created), sum(hours) from memberhour where memberid=460 group by 1 order by 1;
-;an attempt to modify the above query, but make it only report years where more than 100 hours were logged. DOES NOT WORK, needs some fixin.
+;an attempt to modify the above query, to report only years where Martin logged more than 100 hours. DOES NOT WORK, needs some fixin.
 :select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1;
@@ Line 76: / Line 76: @@
 :select count( distinct memberid ) from memberhour;
-;total number of hours logged for each volunteer in each year (yields a very long list)
+;total number of hours logged for each volunteer in each year (yields a very long list!) I'm surprised by number of negative values. Need to look into that.
-:select extract( year from created ), memberid, sum( hours ) from memberhour where memberid in (select distinct memberid from memberhour) group by 1, memberid order by 1;
+:select extract( year from created ) as year, memberid, sum( hours ) from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;

@@ Line 49: / Line 49: @@
 == SQL queries ==
-The following query yields the data for the first column of the table in the main page. Note that the other columns would require a somewhat different query, since each represents a '''range''' of values.
+=== number of money donations, by year and amount ===
-*fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;
+;second column (note alternate syntax for column names, and for range of values)
-*\dt will list tables
+=== number of hours per volunteer, by year ===
-*\d describes a table
+These queries show some of the pieces involved. Still working toward useful queries to use in real life.
-* select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;
+;number of new adoption volunteers signed up, by year
-* select extract( year from created), sum(hours) from memberhour where memberid=5989 group by 1 order by 1; WORKS
+;how many hours Martin volunteered per year
-* select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1; (NEEDS FIXIN)
+;an attempt to modify the above query, but make it only report years where more than 100 hours were logged. DOES NOT WORK, needs some fixin.
-* select count( distinct memberid ) from memberhour;
+;number of folks who have volunteered for us, ever
-* select extract( year from created ), memberid, sum( hours ) from memberhour where memberid in (select count( distinct memberid ) from memberhour) group by 1, memberid order by 1; (NEEDS LOTSA FIXIN)
+;total number of hours logged for each volunteer in each year (yields a very long list)

← Older revision		Revision as of 04:21, 25 January 2006
Line 53:		Line 53:

	fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT() FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;		fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT() FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;
		+
		+	*\dt will list tables
		+	*\d describes a table
		+
		+	select EXTRACT( YEAR FROM created ), count() from contact where adopt='Y' group by 1 order by 1;
		+	ERROR: column "adopt" does not exist
		+	fgdb=> select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;
		+
		+	* select extract( year from created), sum(hours) from memberhour where memberid=5989 group by 1 order by 1; WORKS
		+
		+	* select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1; (NEEDS FIXIN)
		+
		+	* select count( distinct memberid ) from memberhour;
		+
		+	* select extract( year from created ), memberid, sum( hours ) from memberhour where memberid in (select count( distinct memberid ) from memberhour) group by 1, memberid order by 1; (NEEDS LOTSA FIXIN)

@@ Line 50: / Line 50: @@
 == SQL queries ==
-Richard's queries 1/06 for getting number of cash donations over a certain amount:
+Richard's queries 1/06 for getting number of cash donations over a certain amount (first two columns in table on content page):
 *fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;
 *fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 400 GROUP BY 1 ORDER BY 1;