A SQL Query solicited 
Author Message
 A SQL Query solicited

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  

Regards,
Ankur



Wed, 18 Jun 1902 08:00:00 GMT
 A SQL Query solicited

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.



Quote:
> 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>&nbsp;</DIV>
> <DIV><FONT size=3D2>I'm trying to execute&nbsp; the following in a =
> single SQL=20
> query:</FONT></DIV>
> <DIV>&nbsp;</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>&nbsp;</DIV>
> <DIV><FONT size=3D2>I wish to do the following:</FONT></DIV>
> <DIV>&nbsp;</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>&nbsp;</DIV>
> <DIV><FONT size=3D2><STRONG><EM>Date &nbsp;&nbsp;&nbsp; =
> &nbsp;&nbsp;&nbsp;=20
> &nbsp;&nbsp;&nbsp; Expenses &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
> &nbsp;&nbsp;&nbsp; Total Expenses</EM></STRONG></FONT></DIV>
> <DIV>&nbsp;</DIV>
> <DIV><FONT=20

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

20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
p=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;=

- Show quoted text -

Quote:
> &nbsp;&nbsp;&nbsp;&nbsp;=20
> 30</FONT></DIV>
> <DIV><FONT size=3D2>7/10/99&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
> &nbsp;&nbsp;&nbsp; 25&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; =
> &nbsp;&nbsp;&nbsp;=20
> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
> &nbsp;&nbsp;&nbsp;&nbsp; 55</FONT></DIV>
> <DIV><FONT size=3D2>......</FONT></DIV>
> <DIV><FONT size=3D2></FONT>&nbsp;</DIV>
> <DIV><FONT size=3D2><STRONG><EM>Can s'body help executing this query
in =
> a single=20
> SQL statement&nbsp;&nbsp; </EM></STRONG></FONT></DIV>
> <DIV>&nbsp;</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.


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Soliciting thoughts on best way to manage sql table via web

2. How To Solicit Input values via Triggers

3. Soliciting workarounds for concat_null_yields_null bug.

4. Opionions Solicited: NULL values in indexed columns

5. SOLICIT'S FOR YOUR URGENT ATTENTION!!!!!!!!!!!!!!!!!!

6. Solution solicited

7. Lock problems - soliciting suggestions

8. Questions Solicited for 1995 User Conference

9. EDT feedback solicited

10. Dabase design opinions solicited

11. Transportation Control Measure Database-Proposals Solicited

12. Advice Solicited: Desktop Database Selection


 
Powered by phpBB® Forum Software