Re: how to combine columns?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 17 Jul 2001 17:58:50 +0200
Message-ID: <9j1nbr$n6q$1@s1.read.news.oleane.net>

"Scott Crevier" <scott_at_crevier.org> a écrit dans le message news: 3b54536c.31543127_at_news-server.new.rr.com...
> I've got a table of football games with columns for the dates and
> scores. Something like this:
>
> date ourscore theirscore
> --------------------------
> 2001-02-01 23 49
> 2001-02-03 19 14
> 2001-02-08 34 7
> 2001-02-20 9 3
> 2001-02-28 12 12
>
> I need a SELECT statement that will show me if we won, lost or tied
> the game. Something like me this:
>
> 2001-02-01 L
> 2001-02-03 W
> 2001-02-08 W
> 2001-02-20 W
> 2001-02-28 T
>
> I think I'm close. I've tried stuff like:
>
> SELECT date,
> ourscore>theirscore,
> ourscore<theirscore,
> ourscore=theirscore
> from games
>
> But as you can see, that only gives me three columns, each with 1's or
> 0's. So I think I just need to get a bit more fancy with my
> comparisons.
>
> I'd also then like to be able to show our W-L-T record, which in the
> above example would be 3-1-1.
>
> I'm just getting back into DB stuff after about a 5 year absence. I'm
> looking through my old Oracle7 books and I'm not finding anything. I'm
> thinking there's probably a name for something like this, and if I
> only new the name, it would make my searches much more productive.
>
> Would appreciate any assistance.
> --
> Scott Crevier
> http://www.crevier.org/

v815> create table games (gdate date, ourscore integer, theirscore integer);
v815> insert into games values (to_date('2001-02-01','YYYY-MM-DD'),23,49);
v815> insert into games values (to_date('2001-02-03','YYYY-MM-DD'),19,14);
v815> insert into games values (to_date('2001-02-08','YYYY-MM-DD'),34, 7);
v815> insert into games values (to_date('2001-02-20','YYYY-MM-DD'), 9, 3);
v815> insert into games values (to_date('2001-02-28','YYYY-MM-DD'),12,12);
v815> commit;
v815> select to_char(gdate,'YYYY-MM-DD') "Date", ourscore, theirscore

2 from games
3 /

Date OURSCORE THEIRSCORE

---------- ---------- ----------
2001-02-01         23         49
2001-02-03         19         14
2001-02-08         34          7
2001-02-20          9          3
2001-02-28         12         12

5 rows selected.

v815> select to_char(gdate,'YYYY-MM-DD') "Date", 2 decode (sign(ourscore-theirscore),-1,'L',1,'W','T') S 3 from games
4 /

Date S

---------- -
2001-02-01 L
2001-02-03 W
2001-02-08 W
2001-02-20 W
2001-02-28 T

5 rows selected.

v815> select sum(decode(sign(ourscore-theirscore),1,1,0))||'-'||
  2         sum(decode(sign(ourscore-theirscore),-1,1,0))||'-'||
  3         sum(decode(sign(ourscore-theirscore),0,1,0)) S

4 from games
5 /

3-1-1

1 row selected.

--
Have a nice day
Michel

Received on Tue Jul 17 2001 - 10:58:50 CDT