Re: "Voting" question?

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