FGdb SQL queries - Revision history

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

2009-06-19T13:40:15Z

number of hours per volunteer, by year

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

2006-08-15T15:09:08Z

number of money donations, by year and amount

Rfs: /* Basic access to FGdb */

2006-08-15T15:07:11Z

Basic access to FGdb

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

2006-08-15T15:05:32Z

number of hours per volunteer, by year

Halfasspete at 01:36, 31 January 2006

2006-01-31T01:36:06Z

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

2006-01-30T18:51:02Z

number of hours per volunteer, by year

Halfasspete: add categories

2006-01-30T00:49:52Z

add categories

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

2006-01-29T23:51:55Z

number of money donations, by year and amount

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

2006-01-29T23:50:31Z

number of money donations, by year and amount

Halfasspete: straightening up, clarifying

2006-01-29T23:42:47Z

straightening up, clarifying

← Older revision		Revision as of 13:40, 19 June 2009
Line 56:		Line 56:
	[[Category:Coders]]		[[Category:Coders]]
	[[Category:AboutFG]]		[[Category:AboutFG]]
		+	[[Category: FGdb]]

@@ Line 23: / Line 23: @@
 == number of money donations, by year and amount ==
 ;first column of table on main page (Number of donations over $500, by year.)
-:SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE ( cashdonation + monitorfee >= 500 ) GROUP BY 1 ORDER BY 1;
+: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''';
+: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 10: / Line 10: @@
 == Basic access to FGdb ==
-; get an account on '''flowers''' (not live, a little out of date) or '''data''' (live data, needs to be more secure)
+# get an account on '''flowers''' (not live, a little out of date) or '''data''' (live data, needs to be more secure)
-:talk to an [[ASS#Members|ASS]]
+#:talk to an [[ASS#Members|ASS]]
+# log into flowers or data from the command line:
-; log into flowers or data from the command line:
+#:ssh <username>@flowers
-:ssh <username>@flowers
+# run the command line PostgreSQL client, specifying the FGdb database and the fgdb user:
+#:psql fgdb -U fgdb
-; run the command line PostgreSQL client, specifying the FGdb database and the fgdb user:
+# to list the tables of FGdb:
-:psql fgdb -U fgdb
+#:\dt
+# to describe a table (i.e., get info on fields, keys, etc.):
-; to list the tables of FGdb:
+#:\d <tablename>
 == number of money donations, by year and amount ==

@@ Line 36: / Line 36: @@
 ;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;
+:SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM contact WHERE adopter='Y' GROUP BY 1 ORDER BY 1;
 ;how many hours Pete (id 460) volunteered per year
-:select extract( year from created), sum(hours) from memberhour where memberid=460 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, to report only years where Martin (id 5989) logged more than 100 hours.
-:select extract( year from created), sum(hours) as total from memberhour where memberid=5989 group by 1 having sum( hours ) > 100 order by 1;
+:SELECT EXTRACT( YEAR FROM created ), SUM(hours) AS total FROM memberhour WHERE memberid=5989 GROUP BY 1 HAVING SUM( hours ) > 100 ORDER BY 1;
 ;number of folks who have volunteered for us, ever
-:select count( distinct memberid ) from memberhour;
+:SELECT COUNT( DISTINCT memberid ) FROM memberhour;
 ;total number of hours logged for each volunteer in each year (yields a very long list!)
-:select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour group by year, memberid order by year;
+:SELECT EXTRACT( YEAR FROM created ) AS year, memberid, SUM( hours ) AS hours 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;
+: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;
 ;a further refinement. excludes all negative hour entries (which are created when somebody adopts a PC.) So it's a more accurate reflection of the time people contributed.
-:select extract( year from created ) as year, memberid, sum( hours ) as hours from memberhour where hours>0 group by year, memberid having sum( hours )>=80 order by year;
+:SELECT EXTRACT( YEAR FROM created ) AS year, memberid, sum( hours ) AS hours FROM memberhour WHERE hours > 0 GROUP BY year, memberid HAVING SUM( hours ) >=80 ORDER BY year;
 ;next step- figure out how to get a count automatically.

@@ Line 25: / Line 25: @@
 :\d <tablename>
-=== number of money donations, by year and amount ===
+== number of money donations, by year and amount ==
 ;first column of table on main page (Number of donations over $500, by year.)
 :SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE ( cashdonation + monitorfee >= 500 ) GROUP BY 1 ORDER BY 1;
@@ Line 32: / Line 32: @@
 :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 ===
+== 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.

← Older revision		Revision as of 00:49, 30 January 2006
Line 52:		Line 52:
	;a subset of the above query's results, showing all volunteers who logged 80 hours or more in each 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;		: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;
		+
		+	[[Category:Coders]]
		+	[[Category:AboutFG]]

@@ Line 27: / Line 27: @@
 === number of money donations, by year and amount ===
 ;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;
+: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''';
+: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 1: / Line 1: @@
 == Overview ==
-The purpose of this page is to keep SQL queries that have been useful in the past in querying FGdb.
+SQL queries that have been useful for querying [[FGdb]]
-As of now (Jan 2006) this includes queries that are useful in describing Free Geek, to make charts and such to go into the [[Press Kit]]. But any queries that aren't included in the front end can go here.
+As of now (Jan 2006) this includes queries that are useful in describing Free Geek, to make charts and such to go into [[Stats and Testimonials]] (and the [[Press Kit]].) But any useful queries that aren't included in the front end can go here.
 Also, I hope this page will be a resource for people trying to learn more SQL, in addition to gathering data about Free Geek. So queries that are not useful in themselves, but are building blocks for more complex queries, are encouraged.
@@ Line 38: / Line 38: @@
 :select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;
-;how many hours Pete volunteered per year
+;how many hours Pete (id 460) 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.
+;an attempt to modify the above query, to report only years where Martin (id 5989) logged more than 100 hours.
-:select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1;
+:select extract( year from created), sum(hours) as total from memberhour where memberid=5989 group by 1 having sum( hours ) > 100 order by 1;
 ;number of folks who have volunteered for us, ever
@@ Line 48: / Line 48: @@
 ;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;
+:select extract( year from created ) as year, memberid, sum( hours ) as hours 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;
+: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;