FGdb SQL queries

Overview

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 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.

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.)

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 (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 (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;

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!)

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;

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;

next step- figure out how to get a count automatically.