can I do this with one query?

[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...

 

[Gary Walter]

Hi djc,

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

**** 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

 

[djc]

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]

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.

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

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,

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

**** 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

 

[djc]

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.

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.

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

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,

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

**** 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

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...