J
Jay
Can anyone tell me how to do calculations between different Records
(Rows) in a Query?
(Rows) in a Query?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Tom Ellison
Dear Jay:
It's not very clear just what your circumstance is. The primary definition
needed would be how the rows between which you need to make calculations are
related. If the rows are ordered and you want to include the "current row"
with the "previous row" in that sort order, and if the sort order is unique,
then it may be that you would use a correlated subquery.
If you'll be specific about what you want and give a query that shows the
columns with which you are dealing and the name(s) of the table(s), perhaps
I can give some specific assistance.
Tom Ellison
It's not very clear just what your circumstance is. The primary definition
needed would be how the rows between which you need to make calculations are
related. If the rows are ordered and you want to include the "current row"
with the "previous row" in that sort order, and if the sort order is unique,
then it may be that you would use a correlated subquery.
If you'll be specific about what you want and give a query that shows the
columns with which you are dealing and the name(s) of the table(s), perhaps
I can give some specific assistance.
Tom Ellison
J
Jay
Dear Tom
I appreciate your response
the name of Query is Basic_Calcs . I am creating another query from the
existing Query
Unit Name MW Cost What i Want
EL 50 1 1000
EL 50 350 5000 4000
EL 50 400 8000 3000
EL 50 475 10000 2000
EL 50 500 10875 875
SA 10 1 4000 Null (New Unit)
SA 10 300 5000 1000
SA 10 400 5800 800
What i want is the difference between current cost and at 350 MW and
Cost at 1 MW and so on.
I always want this calculation to be with the previous row sorted by
unit.
So i want a null for 1 MW for each unit and for next MW level i want
the difference between the previous and Current MW Level
I would greatly appreciate your response
JAY
I appreciate your response
the name of Query is Basic_Calcs . I am creating another query from the
existing Query
Unit Name MW Cost What i Want
EL 50 1 1000
EL 50 350 5000 4000
EL 50 400 8000 3000
EL 50 475 10000 2000
EL 50 500 10875 875
SA 10 1 4000 Null (New Unit)
SA 10 300 5000 1000
SA 10 400 5800 800
What i want is the difference between current cost and at 350 MW and
Cost at 1 MW and so on.
I always want this calculation to be with the previous row sorted by
unit.
So i want a null for 1 MW for each unit and for next MW level i want
the difference between the previous and Current MW Level
I would greatly appreciate your response
JAY
T
Tom Ellison
Dear Jay:
OK, it looks like it is starting to be possible.
I'm assuming here that this is sorted by UnitName and MW, and that,
together, these columns are unique.
The trick is to be able to find the previous row, when there is one. The
previous row I will define as being a row:
- with the same UnitName
- with the largest value of MW less than the current row
I code that as follows:
SELECT UnitName, MW, Cost,
MW - NZ(SELECT MAX(MW) FROM Basic_Calcs BC1
WHERE BC1.UnitName = BC.UnitName
AND BC1.MW < BC.MW), MW) AS WhatIWant
FROM Basic_Calcs BC
ORDER BY UnitName, MW
The above will give you zero for the first row of each UnitName. Your
sample had this column blank in one instance and Null in another. I'm not
sure what you really want in this instance. I would have thought the
WhatIWant column would have the whole Cost in it when it is the first row,
that is, 1000 in the first one, and 4000 in the second, as the previous row
(which doesn't exist) is effectively zero. That way, a running sum of the
WhatIWant column would be equal to the current Cost value. Makes sense to
me. For this, use:
SELECT UnitName, MW, Cost,
MW - NZ(SELECT MAX(MW) FROM Basic_Calcs BC1
WHERE BC1.UnitName = BC.UnitName
AND BC1.MW < BC.MW), 0) AS WhatIWant
FROM Basic_Calcs BC
ORDER BY UnitName, MW
Does this start to give you what you want?
The technique is known as a correlated subquery, and you must also use
Aliasing. These topics may give you something to search and learn about how
this is done.
Tom Ellison
OK, it looks like it is starting to be possible.
I'm assuming here that this is sorted by UnitName and MW, and that,
together, these columns are unique.
The trick is to be able to find the previous row, when there is one. The
previous row I will define as being a row:
- with the same UnitName
- with the largest value of MW less than the current row
I code that as follows:
SELECT UnitName, MW, Cost,
MW - NZ(SELECT MAX(MW) FROM Basic_Calcs BC1
WHERE BC1.UnitName = BC.UnitName
AND BC1.MW < BC.MW), MW) AS WhatIWant
FROM Basic_Calcs BC
ORDER BY UnitName, MW
The above will give you zero for the first row of each UnitName. Your
sample had this column blank in one instance and Null in another. I'm not
sure what you really want in this instance. I would have thought the
WhatIWant column would have the whole Cost in it when it is the first row,
that is, 1000 in the first one, and 4000 in the second, as the previous row
(which doesn't exist) is effectively zero. That way, a running sum of the
WhatIWant column would be equal to the current Cost value. Makes sense to
me. For this, use:
SELECT UnitName, MW, Cost,
MW - NZ(SELECT MAX(MW) FROM Basic_Calcs BC1
WHERE BC1.UnitName = BC.UnitName
AND BC1.MW < BC.MW), 0) AS WhatIWant
FROM Basic_Calcs BC
ORDER BY UnitName, MW
Does this start to give you what you want?
The technique is known as a correlated subquery, and you must also use
Aliasing. These topics may give you something to search and learn about how
this is done.
Tom Ellison
J
Jay
Thanks Tom