On Sat, Oct 20, 2012 at 6:10 AM, Gary Warner <gar@askgar.com> wrote:
[snip]
> Query for: "Cat"
> Total Alex Bob Carol Dave Ed
> 1 - 4 1 0 1 1 1
> 2 - 3 1 0 1 1 0
> 3 - 0 0 0 0 0 0
> 4 - 1 0 0 1 0 0
> 5 - 4 0 1 1 1 1
> - ------------------------------------
> total 12 2 1 4 3 2
>
[snip]
>
> Is that something I can do IN THE DATABASE with the data in the format that
> I have it stored?
With a table "PhotoVotes" roughly as you described:
Table "public.PhotoVotes"
Column | Type | Modifiers
-----------+---------+-----------
photo_num | integer |
voter | text |
decision | text |
a query like this would do the trick to generate your table, given
your sample data:
WITH distinct_photos AS (
SELECT DISTINCT(photo_num) FROM "PhotoVotes"
),
totals AS (
SELECT photo_num, COUNT(*) AS total_votes
FROM "PhotoVotes" WHERE decision = 'Cat'
GROUP BY photo_num
),
alex_votes AS (
SELECT photo_num, decision
FROM "PhotoVotes" WHERE voter = 'Alex'
)
SELECT dp.photo_num, COALESCE(totals.total_votes, 0) AS total,
(CASE WHEN alex_votes.decision = 'Cat' THEN 1 ELSE 0 END) AS "Alex"
FROM distinct_photos AS dp
LEFT JOIN totals
ON dp.photo_num = totals.photo_num
LEFT JOIN alex_votes
ON alex_votes.photo_num = dp.photo_num
ORDER BY dp.photo_num ASC;
the generation of columns for "Bob", "Carol", "Dave" and "Ed" would be
done the same way as for the "Alex" column. From your message, it
sounded like you knew in advance all the column names you expected to
be generated -- if that's not the case, you might have to look into
something like crosstab().
Josh
