Informix Forum

Re: Computing aggregates

Posted By: Jonathan Leffler
Date: Thursday, 9 March 2006, at 1:49 a.m.

> MESSAGE: (#46) Computing aggregates
> <http://www.iiug.org/forums/informix-forum/index.cgi?rev=46>
> AUTHOR: SAIRA VARGHESE
> DATE: Wednesday, 8 March 2006, at 10:30 a.m.
>
> Hello,
> I am a new Informix user. Please see the sql stmts. below - logdt is DATETIME
> YEAR TO SECOND field...
>
> How do I
>
> 1. count by logdt YEAR TO MINUTE instead of second?

EXTEND(logdt, YEAR TO MINUTE)? What went wrong with that?

>
> 2. calculate the sum of count(*) in the result set ? In other words, what is
>
> the equivalent of compute sum(count(*)) (SYBASE) in Informix?
>
> select count(*), EXTEND(logdt,YEAR TO MINUTE) as logdt
> from data_records
> group by logdt
> order by logdt;

Standard SQL (and Informix's dialect of SQL) does not allow aggregates
of aggregates.

However, SUM(COUNT(*)) is the same a COUNT(*) without the grouping, isn't it?
And it is the same answer in all rows, isn't it?

Didn't you mean GROUP BY 2 or GROUP BY EXTEND(logdt, YEAR TO MINUTE)?
Why would you want to group by individual seconds but only see up to
60 records with the same truncated time and possibly varying numbers
of rows...

Similar comments apply to the ORDER BY clause.

SELECT COUNT(*) FROM data_records; -- overall count

SELECT COUNT(*) AS nrecs, EXTEND(logdt, YEAR TO MINUTE) AS time_slot
FROM data_records
GROUP BY 2
ORDER BY 2;

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Messages In This Thread

• Re: Computing aggregates
  Jonathan Leffler -- Thursday, 9 March 2006, at 1:49 a.m.
  • Re: Computing aggregates
    SAIRA VARGHESE -- Thursday, 9 March 2006, at 10:01 a.m.

Informix Forum is maintained by Administrator with WebBBS 5.12.