can I do this with one query?

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

Below is a copy/paste of my original post. I got one reply and I realize it
is ok to have a query based on a query from that reply. However, I still
want to know if I can do this with one query. And if so, how? I would rather
do things with one query whenever possible for a few reasons, one of them
being I want to learn the SQL syntax, another being that if I get it into
one query than I am more likely able to use that same sql with things other
than access. More portable, if you will. Heres the original post agian:

I have an existing query that already contains a calculated field that uses
the sum() aggregate function. From a table with 1 or several entries for a
particular Item containing start and stop times it sums the total time taken
per Item (each record has an ItemID, ItemType, ItemMake, Start, and Stop
field). So I am already using the sum() function to do this. However, now I
need to get the average time recorded grouped by ItemType.

So, as succinctly put as possible, I need to use the sum() function grouping
by the particular ItemID to get the TotalTime for each individual item. I
have this already. I then need to find the average TotalTime by ItemType.

How can I do this with one query? Do I need to use a sub query? I prefer
using one query if possible as if I understand correctly, that is preferable
over linking several query objects together. ie. SELECT * FROM qrySavedQuery
INNER JOIN tblTableName etc...
 
Hi djc,

Here be snips of a reply I once got
from Steve of RAC ...what is he calling
himself lately? 8-)

**** quote ***
Max(expr) can contain another aggregate function
if the aggregate function is the result of a select.
In other words if the definition of expr includes a query you can do it.

-- An example of nesting aggregate functions.
select max((select sum(b.freight) from orders as b
where b.employeeid=a.employeeid)) as max_frt_sum
from orders as a
where a.orderid in
(select max(c.orderid)
from orders as c
group by c.employeeid);

*** end quote ***

So I believe you can take the Avg in the same query
**if what you are Avg is provided by a select stmt.***

It would be a lot easier to suggest a solution
if you had provided your sum query, but guessing....

SELECT a.ItemType,
SUM( (a.Stop - a.Start)) As TotalTime,
AVG((SELECT SUM(b.Stop-b.Start) FROM
mytable As b WHERE
b.ItemType=a.ItemType)) As AvgTypeTime
FROM mytable As a
GROUP BY
ItemType;

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
very interesting. I am playing with it now, however I am having a problem
using the 'AS varName'. If I understand correctly thats a table alias. My
first query, the one I need to average a field in uses a JOIN. Does that
mean I can't use table aliases?

heres my query. From this result set I need to average the DurationInSec
grouped by ItemType:

SELECT ItemInfo.ItemID, ItemInfo.ItemType,
Sum(DateDiff('s',[StartTime],[StopTime])) AS DurationInSec
FROM ItemInfo INNER JOIN Times ON ItemInfo.ID = Times.ID
GROUP BY ItemInfo.ID, ItemInfo.ItemType;

The result of the above query gives my something like this:

ItemID ItemType DurationInSec
100 B 45
101 B 40
102 C 214
103 D 60
104 D 47

From this result set I need to get the average DurationInSec per ItemType.
Like this:

ItemType AvgDurationInSec
B 42.5
C 214
D 53.5

last note to complete the picture:
The StartTime and StopTime fields are from the Time table. I need to Sum
several records to get to the one record per ItemID showing DurationInSec
for that one item. The Time table contains several start and stop time
entries for one item because what i'm tracking can be started and stopped
many times. I just need the total duration per item. Then I also need total
duration per ItemType (already did no problem), and the average duration per
ItemType. Thats where I'm stuck and thats where I'm at.

Any further assistance is greatly appreciated and thanks again for the
orginal reply. I will continue to work with that.
 
Hi djc,

With caveat that I don't think I
would ever do it this way, here be
one way:

SELECT
a.ItemID,
a.ItemType,
Sum(DateDiff('s',[StartTime],[StopTime])) AS DurationInSec,
(SELECT SUM(DateDiff('s',d.StartTime, d.StopTime))
FROM ItemInfo c INNER JOIN Times d ON c.ID=d.ID
WHERE c.ItemType = a.ItemType
GROUP BY c.ItemType) AS TypeDurationSum,
(SELECT Count(g.ItemType) FROM
(SELECT e.ItemType FROM ItemInfo e INNER JOIN Times f
ON e.ID=f.ID GROUP BY e.ItemID, e.ItemType) g
WHERE g.ItemType = a.ItemType
GROUP BY g.ItemType) AS TypeDurationCount,
[TypeDurationSum]/[TypeDurationCount] AS TypeDurationAvg
FROM ItemInfo AS a INNER JOIN Times AS b ON a.ID = b.ID
GROUP BY a.ItemID, a.ItemType;

Maybe you will see better way from
this example, but my head hurts. 8-)

I'm sure Steve has a *simple* RAC solution
if this were on SQL Server.

Good luck,

Gary Walter

djc said:
very interesting. I am playing with it now, however I am having a problem
using the 'AS varName'. If I understand correctly thats a table alias. My
first query, the one I need to average a field in uses a JOIN. Does that
mean I can't use table aliases?

heres my query. From this result set I need to average the DurationInSec
grouped by ItemType:

SELECT ItemInfo.ItemID, ItemInfo.ItemType,
Sum(DateDiff('s',[StartTime],[StopTime])) AS DurationInSec
FROM ItemInfo INNER JOIN Times ON ItemInfo.ID = Times.ID
GROUP BY ItemInfo.ID, ItemInfo.ItemType;

The result of the above query gives my something like this:

ItemID ItemType DurationInSec
100 B 45
101 B 40
102 C 214
103 D 60
104 D 47

From this result set I need to get the average DurationInSec per ItemType.
Like this:

ItemType AvgDurationInSec
B 42.5
C 214
D 53.5

last note to complete the picture:
The StartTime and StopTime fields are from the Time table. I need to Sum
several records to get to the one record per ItemID showing DurationInSec
for that one item. The Time table contains several start and stop time
entries for one item because what i'm tracking can be started and stopped
many times. I just need the total duration per item. Then I also need total
duration per ItemType (already did no problem), and the average duration per
ItemType. Thats where I'm stuck and thats where I'm at.

Any further assistance is greatly appreciated and thanks again for the
orginal reply. I will continue to work with that.


Gary Walter said:
Hi djc,

Here be snips of a reply I once got
from Steve of RAC ...what is he calling
himself lately? 8-)

**** quote ***
Max(expr) can contain another aggregate function
if the aggregate function is the result of a select.
In other words if the definition of expr includes a query you can do it.

-- An example of nesting aggregate functions.
select max((select sum(b.freight) from orders as b
where b.employeeid=a.employeeid)) as max_frt_sum
from orders as a
where a.orderid in
(select max(c.orderid)
from orders as c
group by c.employeeid);

*** end quote ***

So I believe you can take the Avg in the same query
**if what you are Avg is provided by a select stmt.***

It would be a lot easier to suggest a solution
if you had provided your sum query, but guessing....

SELECT a.ItemType,
SUM( (a.Stop - a.Start)) As TotalTime,
AVG((SELECT SUM(b.Stop-b.Start) FROM
mytable As b WHERE
b.ItemType=a.ItemType)) As AvgTypeTime
FROM mytable As a
GROUP BY
ItemType;

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
wow! mine hurts too ; )

Thank you very much for all your help. I will play with this as well. you
have given me good direction.


Gary Walter said:
Hi djc,

With caveat that I don't think I
would ever do it this way, here be
one way:

SELECT
a.ItemID,
a.ItemType,
Sum(DateDiff('s',[StartTime],[StopTime])) AS DurationInSec,
(SELECT SUM(DateDiff('s',d.StartTime, d.StopTime))
FROM ItemInfo c INNER JOIN Times d ON c.ID=d.ID
WHERE c.ItemType = a.ItemType
GROUP BY c.ItemType) AS TypeDurationSum,
(SELECT Count(g.ItemType) FROM
(SELECT e.ItemType FROM ItemInfo e INNER JOIN Times f
ON e.ID=f.ID GROUP BY e.ItemID, e.ItemType) g
WHERE g.ItemType = a.ItemType
GROUP BY g.ItemType) AS TypeDurationCount,
[TypeDurationSum]/[TypeDurationCount] AS TypeDurationAvg
FROM ItemInfo AS a INNER JOIN Times AS b ON a.ID = b.ID
GROUP BY a.ItemID, a.ItemType;

Maybe you will see better way from
this example, but my head hurts. 8-)

I'm sure Steve has a *simple* RAC solution
if this were on SQL Server.

Good luck,

Gary Walter

djc said:
very interesting. I am playing with it now, however I am having a problem
using the 'AS varName'. If I understand correctly thats a table alias. My
first query, the one I need to average a field in uses a JOIN. Does that
mean I can't use table aliases?

heres my query. From this result set I need to average the DurationInSec
grouped by ItemType:

SELECT ItemInfo.ItemID, ItemInfo.ItemType,
Sum(DateDiff('s',[StartTime],[StopTime])) AS DurationInSec
FROM ItemInfo INNER JOIN Times ON ItemInfo.ID = Times.ID
GROUP BY ItemInfo.ID, ItemInfo.ItemType;

The result of the above query gives my something like this:

ItemID ItemType DurationInSec
100 B 45
101 B 40
102 C 214
103 D 60
104 D 47

From this result set I need to get the average DurationInSec per ItemType.
Like this:

ItemType AvgDurationInSec
B 42.5
C 214
D 53.5

last note to complete the picture:
The StartTime and StopTime fields are from the Time table. I need to Sum
several records to get to the one record per ItemID showing DurationInSec
for that one item. The Time table contains several start and stop time
entries for one item because what i'm tracking can be started and stopped
many times. I just need the total duration per item. Then I also need total
duration per ItemType (already did no problem), and the average duration per
ItemType. Thats where I'm stuck and thats where I'm at.

Any further assistance is greatly appreciated and thanks again for the
orginal reply. I will continue to work with that.


Gary Walter said:
Hi djc,

Here be snips of a reply I once got
from Steve of RAC ...what is he calling
himself lately? 8-)

**** quote ***
Max(expr) can contain another aggregate function
if the aggregate function is the result of a select.
In other words if the definition of expr includes a query you can do it.

-- An example of nesting aggregate functions.
select max((select sum(b.freight) from orders as b
where b.employeeid=a.employeeid)) as max_frt_sum
from orders as a
where a.orderid in
(select max(c.orderid)
from orders as c
group by c.employeeid);

*** end quote ***

So I believe you can take the Avg in the same query
**if what you are Avg is provided by a select stmt.***

It would be a lot easier to suggest a solution
if you had provided your sum query, but guessing....

SELECT a.ItemType,
SUM( (a.Stop - a.Start)) As TotalTime,
AVG((SELECT SUM(b.Stop-b.Start) FROM
mytable As b WHERE
b.ItemType=a.ItemType)) As AvgTypeTime
FROM mytable As a
GROUP BY
ItemType;

Please respond back if I have misunderstood.

Good luck,

Gary Walter

Below is a copy/paste of my original post. I got one reply and I
Click to expand...
realize
it
is ok to have a query based on a query from that reply. However, I still
want to know if I can do this with one query. And if so, how? I
Click to expand...
would
rather
do things with one query whenever possible for a few reasons, one of them
being I want to learn the SQL syntax, another being that if I get it into
one query than I am more likely able to use that same sql with
Click to expand...
things
other
than access. More portable, if you will. Heres the original post agian:

I have an existing query that already contains a calculated field
Click to expand...
that
uses
the sum() aggregate function. From a table with 1 or several entries
Click to expand...
for
a
particular Item containing start and stop times it sums the total
Click to expand...
time
taken
per Item (each record has an ItemID, ItemType, ItemMake, Start, and Stop
field). So I am already using the sum() function to do this.
Click to expand...
However,
now I
need to get the average time recorded grouped by ItemType.

So, as succinctly put as possible, I need to use the sum() function grouping
by the particular ItemID to get the TotalTime for each individual
Click to expand...
item.
I
have this already. I then need to find the average TotalTime by ItemType.

How can I do this with one query? Do I need to use a sub query? I prefer
using one query if possible as if I understand correctly, that is preferable
over linking several query objects together. ie. SELECT * FROM qrySavedQuery
INNER JOIN tblTableName etc...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top