Sum of a Count

[Amin]

Hi,
So this is my code:

SELECT (EVENTTIME\10000)+1 AS TimeInterval, Round(Count(ID)/3,0) AS Workloads
FROM table
GROUP BY (EVENTTIME\10000);

This produces the following:

TimeInterval Workloads
6 100
7 300
8 450
9 350
10 500

What I would like to do is insert a row at the end that will sum the
workloads for the entire day, without having to create a new query. So it
would look like this:

TimeInterval Workloads
6 100
7 300
8 450
9 350
10 500
"total" 1700

Can this be done? Whenver I try to SUM(COUNT(... I get an error. Thanks for
any thoughts in advance!

Amin

 

[Sheila D]

You could use your query as the basis for a report and then use a calculated
field / text box to sum the values......

 

[Marshall Barton]

SELECT (EVENTTIME\10000)+1 AS TimeInterval, Round(Count(ID)/3,0) AS Workloads
FROM table
GROUP BY (EVENTTIME\10000);

This produces the following:

TimeInterval Workloads
6 100
7 300
8 450
9 350
10 500

What I would like to do is insert a row at the end that will sum the
workloads for the entire day, without having to create a new query. So it
would look like this:

TimeInterval Workloads
6 100
7 300
8 450
9 350
10 500
"total" 1700

Can this be done? Whenver I try to SUM(COUNT(... I get an error.

You can so that by using a UNION query, but that's not a
good idea. You should never expose a table or query's
datasheet to users. Formatted/calculated data needs to be
presented to users through a form or report. Then you can
use the Sum function in the form/report header/footer
section.

 

[Amin]

Thank you Marsh. I do know how to put that in a report, but I could not
figure out how to solve the problem with a union. So how would I write the
code? I am asking to understand SQL better because I continue to get an error
whenever I try the Sum of a Count.

Thank you,
Amin

 

[Marshall Barton]

I was thinking of this kind of thing:

SELECT (EVENTTIME\10000)+1 AS TimeInterval,
Round(Count(ID)/3,0) AS Workloads
FROM table
GROUP BY (EVENTTIME\10000)+1
UNION ALL
SELECT Null, Round(Count(*) / 3, 0)
FROM table

BUT, the Round function throws that out because. the sum of
round is not the same as round of sum.

There may be a tricky way to do it in one query, but I think
it would be better to do it with two queries. Let's say the
base query you posted earlier is named MyWorkloads, then you
can use

SELECT TimeInterval, Workloads FROM MyWorkloads
UNION ALL
SELECT Null, Sum(Workloads) FROM MyWorkloads