How about something like:
SELECT rc_cd, rp_cd, votes FROM results
UNION ALL
SELECT rc_cd, 'TOTAL' AS rp_cd, sum(votes)
AS votes FROM results GROUP BY rc_cd
ORDER BY rc_cd ASC, rp_cd ASC;
But this kind of thing might be better done outside of SQL.
--- Robert L Cochran <[EMAIL PROTECTED]> wrote:
> I have a simple table containing 3 columns: a political candidate code,
> a precinct code, and the number of votes that candidate recieved for
> that precinct. Here is a sample:
>
> sqlite> select rc_cd, rp_cd, votes from results order by rc_cd asc,
> rp_cd asc;
> ivy000|1|266
> ivy000|2|252
> ivy000|3|30
> ivy000|4|219
> ivy000|5|38
> put000|1|494
> put000|2|472
> put000|3|39
> put000|4|385
> put000|5|88
>
> When a candidate code changes, I want to insert a subtotal of the
> candidate's total votes across all precincts in the result set:
>
> ...
> ivy000|5|38
> <--- subtotal for 'ivy000' goes here in the results stream
> put000|1|494
> ...
> put000|5|88
> <--- subtotal for 'put000' goes here in the results stream
>
> How can I do this in SQLite? It doesn't look to me like the group by
> functionality exists yet for it, but then again my SQL crafting skills
> need a lot of polishing and I could be wrong.
>
> Thanks
>
> Bob Cochran
>
>
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
