tricky SQL question: how to return "virtual" computed columns

I'd like to return colums that don't actually correspond to columns in my table, but instead are computed from a group of rows within the table.

For example, assume the table:

NAME SCORE DAY
------- --------- ------

david    11        monday
david    10        monday
david    5          monday
mark    4          tuesday
mark    9          monday
eric      5          monday
eric      11        wednesday

I want to return one row per NAME, with two columns: one col for the sum(score) for all rows for that name, and one col for the sum(score) for rows matching that name and also matching a particular day of week.

so, for example, the query to get me david's total score, and total score for all mondays would return:
david 26 21

if david didn't have any scores from any mondays, then this query could return no rows.

I know I can do it in two queries:
SELECT name, SUM(score) FROM mytable WHERE DAY=monday GROUP BY day; SELECT name, SUM(score) FROM mytable WHERE NAME IN(SELECT name FROM mytable where DAY=monday) GROUP BY day;

Is this possible in a single SQL statement?

Thanks!

David
--

David Michaels <david_at_shockmarket.com>
Director of Technology
ShockMarket Corporation (650) 330-4665

Received on Thu Jun 24 1999 - 23:08:17 CDT