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

Hi,

This would give you 'nearly' the correct:

select name, sum(score),

    sum(decode(day,'monday',score,0)) monday,     sum(decode(day,'tuesday',score,0)) tuesday     ...
    sum(decode(day,'sunday',score,0)) sunday from mytable
group by day;

The output would look something like:

NAME SCORE MONDAY TUESDAY WEDENSDAY....SUNDAY

------- --------- ------------ ------------ ----------------    -----------

david             26               26
0                       0                   0
mark              13                 9
4                       0                   0
eric                16                 5
0                      11                  0

Hope this helps!

Regards

Michael Ringbo

David Michaels wrote:

> 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 Fri Jun 25 1999 - 03:29:11 CDT