advanced SQL question

[djc]

I have posted previously on this and have made progress as far as learning
what the language can and can't do. However I have not been able to really
do this yet. I need to take the avg of a column that has already been
summed. The whole can't use agregate function within another aggregate
function restriction. I know I can get my end result very easily from just
breaking it into 2 queries but I really want to be able to do it in one
query. It's an academic persuit at this point. I have already implemented
the 2 query approach for production. But I really want to be able to do it
in one query. More specifically, I really want to learn the advanced syntax
of the sql language.

here we go:
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.

new note: first goal is to have 2 queries. One that gives my total per
itemtype (done already) and the other to get average by itemtype (problem),
however if someone could actually get all this in one query that would be
even better.

any experts out there?

 

[Ken Snell]

Try this to see if it works for you (may run slow!):

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

 

[djc]

just wanted to give a quick reply to thank you for your reply. I just had
some other issues come up that I need to attend to and have not been able to
try this out. But I will and I will post back when I can to let you know how
it goes.

Thank you very much!

Try this to see if it works for you (may run slow!):

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

--
Ken Snell
<MS ACCESS MVP>

I have posted previously on this and have made progress as far as learning
what the language can and can't do. However I have not been able to really
do this yet. I need to take the avg of a column that has already been
summed. The whole can't use agregate function within another aggregate
function restriction. I know I can get my end result very easily from just
breaking it into 2 queries but I really want to be able to do it in one
query. It's an academic persuit at this point. I have already implemented
the 2 query approach for production. But I really want to be able to do it
in one query. More specifically, I really want to learn the advanced syntax
of the sql language.

here we go:
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.

new note: first goal is to have 2 queries. One that gives my total per
itemtype (done already) and the other to get average by itemtype (problem),
however if someone could actually get all this in one query that would be
even better.

any experts out there?