Informix Forum

Re: Computing aggregates

Posted By: Jonathan Leffler
Date: Friday, 10 March 2006, at 1:05 a.m.

SAIRA VARGHESE wrote:
> Thanks for taking some time to respond... I developed a program that would
> insert a large # of rows into a table. This script is meant to calculate the
> total # of rows inserted in a single run of the program (there are rows in the
> table from previous runs) and # of rows / minute. You are right, sum(count(*))
> wouldn't work.
>
> I tried the following script and it gave me a syntax error at the character
> indicated by the carat sign - I think it is not allowing any function call in
> group by statement because I tried 'group by DATE(logdt)' which yielded the
> same result; 'group by 2' works fine.

I don't recall you saying which version of IDS you're using; IDS 10.00
has some enhancements in this area, IIRC - unless it was in 9.40.
Always include the version information; it helps people like me
respond more accurately.

> select count(*), EXTEND(logdt, YEAR TO MINUTE) as logdt
> from air_records
> group by 2, order by 2;
> --group by logdt order by logdt;
> --group by EXTEND(logdt,YEAR TO MINUTE)
>
> ^
> --group by DATE(logdt)
> Result is below:
>
> 3764 2006-03-07 14:30
> 10185 2006-03-07 14:31
> 1158 2006-03-07 14:32
> Now, is there any way to calculate the sum of the three counts in the script?

Yes - which way do you want to do it, and are there any constraints on us?

Option 1:

Add an 'INTO TEMP temp_data' clause and give the count column a name
(AS log_count, perhaps), and then get the base answers with 'SELECT *
FROM temp_data' and the aggregate with 'SELECT SUM(log_count) FROM
temp_data'?

Option 2:

Keep the base query as you have it now. Run a second query 'SELECT
COUNT(*) FROM air_records' to get the aggregate. This might well be
quicker if the second select can be left without any WHERE clause -
IDS can optimize that without needing to scan the whole table.

Option 3: Probably - but I don't think you need extra ways at this point.

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

Informix Forum is maintained by Administrator with WebBBS 5.12.