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

Oh yes, I did make a typo for "davids' scores in my example, the third entry for david should read 'tuesday'

thanks for all the great help!

Kenny Gump wrote:

> 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
> >
> >

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

Received on Fri Jun 25 1999 - 15:11:07 CDT