Re: SQL Count

->Message-Id: <199204211959.AA06708@tbird.cc.bellcore.com> ->From: uunet!moscow.cc.bellcore.com!foxj (fox,james p) ->Date: 21 Apr 1992 15:50 EDT ->Subject: SQL Count ->X-Informix-List-Id: <list.1083> -> -> I have come across what seems like a simple problem but I can't ->think of an easy solution to it. -> ->I have a table with the following columns: -> FIELD1 -> FIELD2 -> FIELD3 -> ->and an Ace report select of: -> SELECT UNIQUE FIELD1, FIELD2 from table; -> ->Before I execute this report I would like to know how many rows ->will be selected but it seems impossible to use count as I ->cannot specify two fields on the UNIQUE. By the way I am using ->ESQL/C. -> ->I know I could do it by selecting into a "temp" table but I'm ->concerned about the performance. I don't think there is a solution to this using ANSI standard SQL, nor using Informix's extensions except, as you say, by using a temporary table and doing the count on that. Incidentally, you could run the main report query against the temporary table; you could also create an index on it if that would help sufficiently. How accurately do you need to know the answer? You have two choices: approximately and exactly. If it is exactly, you are stuck. If approximately will do, and you are using a sufficiently recent version of ESQL/C (I'm not sure whether that means 4.10 or 5.00, and someone's borrowed my manual), then after one of the PREPARE, DECLARE and OPEN statements, sqlca.sqlerrd[0] will contain an estimate of the number of rows returned. (I think it is the OPEN statement, but I stand to be corrected.) Provided that you run UPDATE STATISTICS sufficiently frequently, this will give a moderate estimate of the number of rows which will be returned. Yours, Jonathan Leffler (johnl@obelix.informix.com)