Re: A SQL Query solicited

From: <michael_bialik_at_my-deja.com>
Date: Sat, 09 Oct 1999 13:01:14 GMT
Message-ID: <7tnean$asf$1@nnrp1.deja.com>

Hi.

Try following :

SELECT viz_date, sum(expenses) exp,
(sum(total)+ sum(expenses) total
FROM (
SELECT viz_date, sum(expenses) expenses, 0 total

     FROM tab_a
     GROUP BY viz_date

UNION
SELECT t1.viz_date, 0 , sum(t2.expenses)/2 total

     FROM tab_a t2, tab_a t1
     WHERE t2.viz_date < t1.viz_date
     GROUP BY t1.viz_date, 0 )

GROUP BY viz_date;

I would not bet on performance of it, but it seems to work.

HTH. Michael.

In article <7tksiv$vjl$1_at_nntpd.lkg.dec.com>, "Ankur Gupta" <ankurg_at_swecexc.xko.dec.com> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0008_01BF11C4.8303F4C0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Dear All,
>
> I'm trying to execute the following in a single SQL query:
>
> I've a table say (Table1) which has two columns viz. date and
expenses =
> (none of these keys being unique ).
>
> I wish to do the following:
>
> I want to sort this table on date field and get the total expenses
done =
> till that date. The end result should be like this
>
> Date Expenses Total Expenses
>
> 5/10/99 10 10
> 6/10/99 20 30
> 7/10/99 25 55
> ......
>
> Can s'body help executing this query in a single SQL statement =20
>
> Regards,
> Ankur
>
> ------=_NextPart_000_0008_01BF11C4.8303F4C0
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT size=3D2>Dear All,</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>I'm trying to execute  the following in a =
> single SQL=20
> query:</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>I've a table say (Table1) which has two columns
viz. =
> date and=20
> expenses (none of these keys being unique ).</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>I wish to do the following:</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>I want to sort this table on date field and get
the =
> total=20
> expenses done <STRONG><EM>till that date. </EM></STRONG>The end
result =
> should be=20
> like <STRONG><EM>this</EM></STRONG></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2><STRONG><EM>Date     =
>    =20
>     Expenses        =20
>     Total Expenses</EM></STRONG></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT=20
>

size=3D2>5/10/99         &n b=
> sp; =20
> 10            =
>    =20
>            
10</FONT></DIV>
> <DIV><FONT size=3D2>6/10/99       =20
>    =20
>

20           &nbs p=
>

;           &nbsp ;=
>     =20
> 30</FONT></DIV>
> <DIV><FONT size=3D2>7/10/99       =20
>     25        =
>    =20
>            =20
>      55</FONT></DIV>
> <DIV><FONT size=3D2>......</FONT></DIV>
> <DIV><FONT size=3D2></FONT> </DIV>
> <DIV><FONT size=3D2><STRONG><EM>Can s'body help executing this query
in =
> a single=20
> SQL statement   </EM></STRONG></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>Regards,</FONT></DIV>
> <DIV><FONT size=3D2>Ankur</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_0008_01BF11C4.8303F4C0--
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 09 1999 - 08:01:14 CDT