Newbie: looping calculations in SQL

Hi, I have the following table:
Year I J Amount
1990 124 220 13.45
1990 124 221 -3.45
...
1990 148 190 123.4
...
1994 124 220 4.44
1994 124 221 -3.45
..
etc.
These are results coming from a VB calculation and too large to store in
memory, otherwise the calculations would be easy through an array. So i am
curious if they can be done relatively easily through SQL.
Basically for a range of years, i have a series of pairs ( I , J ) for each
year as shown in the example and a corresponding Amount for each pair. The
*same* pairs for every year !
Is there a way in SQL to e.g. add all the amounts for each year with the
same I,J ?
I need to find maxima, minima, smallest negative, averages, etc.
Basically i need to apply the equivalent of a loop. I need from my front end
how big the table is and how many pairs i have per year.
What worries me most is the equivalent of IF statements. For example for a
*specific* (I,J) pair scan all years and then find which year has the
smallest amount .
Any suggestions would be greately appreciated!
TIA
-steveSelect Year,I,Sum(Amount) from Table1 group by Year, I -- This will
give the sum of amount for each year for the same I.
similarly you can do
Select Year,J,Sum(Amount) from Table1 group by Year, J
And also
Select Year,I,max(Amount) from Table1 group by Year, I
You can check the other SQL aggregate functions .
Hope this helps|||"smith" <jsmith@.yahoo.ca> wrote in message
news:CkBbf.3676$EK.87849@.news20.bellglobal.com...
> Hi, I have the following table:
> Year I J Amount
> 1990 124 220 13.45
> 1990 124 221 -3.45
> ...
> 1990 148 190 123.4
> ...
> 1994 124 220 4.44
> 1994 124 221 -3.45
> ..
> etc.
>
> These are results coming from a VB calculation and too large to store in
> memory, otherwise the calculations would be easy through an array. So i am
> curious if they can be done relatively easily through SQL.
> Basically for a range of years, i have a series of pairs ( I , J ) for
> each year as shown in the example and a corresponding Amount for each
> pair. The *same* pairs for every year !
> Is there a way in SQL to e.g. add all the amounts for each year with the
> same I,J ?
> I need to find maxima, minima, smallest negative, averages, etc.
> Basically i need to apply the equivalent of a loop. I need from my front
> end how big the table is and how many pairs i have per year.
> What worries me most is the equivalent of IF statements. For example for a
> *specific* (I,J) pair scan all years and then find which year has the
> smallest amount .
> Any suggestions would be greately appreciated!
> TIA
> -steve
>
SELECT year, i, j,
MIN(amount), MAX(amount), AVG(amount)
FROM your_table
GROUP BY year, i, j ;
David Portas
SQL Server MVP
--|||Hi Smith,

same I,J ?
Select [Year], I, J, Sum(Amount) from TestTable
Group by [Year], I, J
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"smith" wrote:
> Hi, I have the following table:
> Year I J Amount
> 1990 124 220 13.45
> 1990 124 221 -3.45
> ...
> 1990 148 190 123.4
> ...
> 1994 124 220 4.44
> 1994 124 221 -3.45
> ...
> etc.
>
> These are results coming from a VB calculation and too large to store in
> memory, otherwise the calculations would be easy through an array. So i am
> curious if they can be done relatively easily through SQL.
> Basically for a range of years, i have a series of pairs ( I , J ) for eac
h
> year as shown in the example and a corresponding Amount for each pair. The
> *same* pairs for every year !
> Is there a way in SQL to e.g. add all the amounts for each year with the
> same I,J ?
> I need to find maxima, minima, smallest negative, averages, etc.
> Basically i need to apply the equivalent of a loop. I need from my front e
nd
> how big the table is and how many pairs i have per year.
> What worries me most is the equivalent of IF statements. For example for a
> *specific* (I,J) pair scan all years and then find which year has the
> smallest amount .
> Any suggestions would be greately appreciated!
> TIA
> -steve
>
>|||This does not work.
Is it because i am using Access? I went to the SQL editor and entered the
command.
It basically returns the same number!
What i want based on my example is the following:
Year I J Amount AvYears
MaxOverYears NumPositive ....etc
1990 124 220 13.45 (13.45 +...+4.44)/5 13.45
3
1990 124 221 -3.45
...
1990 148 190 123.4
...
1994 124 220 4.44 (13.45 +...+4.44)/5 13.45
3
1994 124 221 -3.45
where AvYears is the average for the same pair. in the example line 13.45
for 1990 + ... + 4.44 for 1994) / num of years =5 in this case.
MaxOverYears = the highest value for the same pair over all years, again in
the example i assume that in 1990 i had the maximum which is 13.45.
NumPositive is a count of how many years there were positive number for the
same pair. Lets assume 1990.1992 and 1994 were positive so that makes 3
years.
etc.
I couldnt find as good a newsgroup for JetSQL as this one and i am assuming
that a generic SQL would do it. (if it can be done relatively easily).
Thanx again!
-steve
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:64A4FA9E-5FF6-45B9-925C-A78957723F44@.microsoft.com...
> Hi Smith,
>
> same I,J ?
> Select [Year], I, J, Sum(Amount) from TestTable
> Group by [Year], I, J
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "smith" wrote:
>|||On Mon, 7 Nov 2005 00:09:51 -0500, smith wrote:

>Hi, I have the following table:
>Year I J Amount
>1990 124 220 13.45
>1990 124 221 -3.45
>...
>1990 148 190 123.4
>...
>1994 124 220 4.44
>1994 124 221 -3.45
>..
>etc.
>
>These are results coming from a VB calculation and too large to store in
>memory, otherwise the calculations would be easy through an array. So i am
>curious if they can be done relatively easily through SQL.
>Basically for a range of years, i have a series of pairs ( I , J ) for each
>year as shown in the example and a corresponding Amount for each pair. The
>*same* pairs for every year !
>Is there a way in SQL to e.g. add all the amounts for each year with the
>same I,J ?
>I need to find maxima, minima, smallest negative, averages, etc.
Hi Smith,
SELECT I, J,
MAX(Amount) AS Maximum,
MIN(Amount) AS Minimum,
MAX(CASE WHEN Amount < 0 THEN Amount END) AS SmallestNegative,
AVG(Amount) AS Average
FROM YourTable
GROUP BY I, J
(Untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||yes thats better!
How can i add the year though?
You see, this command "groups by" in the output as well.
I want to "group by" in the calculations but have the same number of lines
as the original table.
I know there will be repetitions for the different years.
Thanx again
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s832n1hkjt5ji3bu4hiak6iq75dvnbf75a@.
4ax.com...
> On Mon, 7 Nov 2005 00:09:51 -0500, smith wrote:
>
> Hi Smith,
> SELECT I, J,
> MAX(Amount) AS Maximum,
> MIN(Amount) AS Minimum,
> MAX(CASE WHEN Amount < 0 THEN Amount END) AS SmallestNegative,
> AVG(Amount) AS Average
> FROM YourTable
> GROUP BY I, J
> (Untested - see www.aspfaq.com/5006 if you prefer a tested solution)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Nov 2005 23:23:29 -0500, smith wrote:

>yes thats better!
>How can i add the year though?
>You see, this command "groups by" in the output as well.
>I want to "group by" in the calculations but have the same number of lines
>as the original table.
>I know there will be repetitions for the different years.
Hi Smith,
That's a lot harder to do, since the data you want to return is not
normalized. However, there are ways to get this data.
The straightforward (but not nice, nor efficient) method is to use
subqueries in the SELECT clause:
SELECT a.Year, a.I, a.J,
(SELECT MAX(Amount)
FROM YourTable AS b
WHERE b.I = a.I
AND b.J = a.J) AS Maximum,
(SELECT MIN(Amount)
FROM YourTable AS b
WHERE b.I = a.I
AND b.J = a.J) AS Minimum,
...
FROM YourTable AS a
And the more efficient (but harder to understand) way uses the query I
posted yesterday as a dericed table:
SELECT a.Year, a.I, a.J,
b.Maximum, b.Minimum,
...
FROM YourTable AS a
INNER JOIN (SELECT I, J,
MAX(Amount) AS Maximum,
MIN(Amount) AS Minimum,
...
FROM YourTable
GROUP BY I, J) AS b
ON b.I = a.I
AND b.J = a.J
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)