FGdb SQL queries

Overview

The purpose of this page is to keep SQL queries that have been useful in the past in 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.

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.

Pete

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)

talk to an ASS

log into flowers or data from the command line

ssh <username>@flowers

run the command line PostgreSQL client, specifying the FGdb database and the fgdb user

psql fgdb -U fgdb

to list the tables of FGdb

\dt

to describe a table (i.e., get info on fields, keys, etc.)

\d <tablename>

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;

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;