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

From: Kenny Gump <kgump_at_mylanlabs.com.nospam>
Date: Fri, 25 Jun 1999 11:26:03 -0400
Message-ID: <37739e94.0@news.mountain.net>

Try something like this:

select a.name, b.nscore, c.nmscore
from mytable a, (select name, sum(score) nscore

                             from mytable
                             group by name) b,
                            (select name, day, sum(score) nmscore
                              from mytable
                             group by name, day) c
where a.name = b.name and
            a.name = c.name

order by a.name;

Kenny Gump

David Michaels wrote in message <377300B1.299EFC93_at_shockmarket.com>...
>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 - 10:26:03 CDT