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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 25 Jun 1999 11:36:59 GMT
Message-ID: <377868c9.61482667@newshost.us.oracle.com>

A copy of this was sent to David Michaels <david_at_shockmarket.com> (if that email address didn't require changing) On Thu, 24 Jun 1999 21:08:17 -0700, you 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.
>

select name, sum(score), sum( decode(day,:x,score,0) )   from T
 group by name
/

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

no, david is all monday -- it returns 26, 26 but eric and mark return different values:

SQL> create table t ( name varchar2(25), score number, day varchar2(25) ); Table created.

SQL> insert into t values ( 'david',    11,'monday' );
SQL> insert into t values ( 'david',    10,'monday' );
SQL> insert into t values ( 'david',    5,'monday' );
SQL> insert into t values ( 'mark',       4,'tuesday' );
SQL> insert into t values ( 'mark',       9,'monday' );
SQL> insert into t values ( 'eric',      5,'monday' );
SQL> insert into t values ( 'eric',      11,'wednesday' );


SQL> variable x varchar2(25)

SQL>
SQL> exec :x := 'monday'

PL/SQL procedure successfully completed.

SQL>
SQL> select name, sum(score), sum( decode(day,:x,score,0) )   2 from T
  3 group by name
  4 /

NAME                           SUM(SCORE) SUM(DECODE(DAY,:X,SCORE,0))
------------------------------ ---------- ---------------------------
david                                  26                          26
eric                                   16                           5
mark                                   13                           9

>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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 25 1999 - 06:36:59 CDT