More on aggregate subtotals

More on aggregate subtotals

Post by Robert Taylo » Wed, 10 Dec 2003 21:57:08

I'm continuing to work on subtotals by date range.  David Portas
yesterday prodided me with the following, which is great way to do
subtotals by month!  

FROM tblAuditLog
GROUP BY YEAR(fldEventTime),MONTH(fldEventTime),eventstring
having fldEventString='user created'
ORDER BY YEAR(fldEventTime),MONTH(fldEventTime)>>

But, I need to be able to add a date range.  However, adding
fldEventTime to the HAVING clause creates the error...
'fldEventTime' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause'.  This
makes sense.  My normal solution to this would be to use another select
statement as the basis of my recordset.

), fldEventTime
FROM (select * from tblAuditLog where eventTime <=getdate()) rs
GROUP BY YEAR(fldEventTime),MONTH(fldEventTime),fldeventstring

having fldEventString='user created'
ORDER BY YEAR(fldEventTime),MONTH(fldEventTime)

My question is, 'is there a shorter/better solution than this'?

Thanks to all.


*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!


More on aggregate subtotals

Post by David Porta » Thu, 11 Dec 2003 00:32:12

I think WHERE is what you are looking for, not HAVING.

SELECT DATENAME(MONTH,MIN(eventtime))+STR(YEAR(MIN(eventTime)),5),
 COUNT(*), MIN(eventtime)
 FROM tblAuditLog
  AND eventstring='user created'
 GROUP BY YEAR(eventtime), MONTH(eventtime)
 ORDER BY YEAR(eventtime), MONTH(eventtime)

David Portas
Please reply only to the newsgroup


1. Aggregate column and non-aggregate column

How do you get an aggregate value (such as MAX(last_visit_date)) and a
non-aggregate value (such as date_of_birth) in a single query?  I
should know this, but I've just been staring at this for too long and
I need to move on.

suppose the following DDL, although this question could apply to any

create table transactions (
  transaction_id int not null,
  account_id int not null,
  entry_date datetime not null,
  amount money not null)

create table accounts (
  account_id int not null)

INSERT accounts VALUES (1)

INSERT transactions VALUES (1, 1, '', 50.00)
INSERT transactions VALUES (1, 1, '', 50.00)
INSERT transactions VALUES (1, 1, '', 50.00)
INSERT transactions VALUES (1, 1, '', 50.00)
INSERT transactions VALUES (1, 1, '', 50.00)
INSERT transactions VALUES (1, 1, '', 50.00)

"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]

2. Check if a user is really connected

3. Help! Aggregate on aggregate

4. Access -> SQL Server = BIG DB

5. Aggregating an aggregated result without COMPUTE?

6. Changing a database design which has an active publication

7. Combining aggregate and non-aggregate values

8. A Simple Question I'm sure

9. Aggregate on Aggregate

10. SQL: Aggregate within in aggregate?

11. Can you have an aggregate function over an aggregate

12. How to get finer control of subtotals when summarizing Data Using ROLLUP?This query generates a subtotal report:

13. How can I generate running subtotals