Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Unanswered: Get the rows of a sql query

    I am developing a component that makes a list with pagination, in order to do that I have an sql generator.

    I need to get the number of rows that the sql query will produce, but I need to count by several columns:

    SELECT COUNT(A.id, B.id, ...)
    FROM table1 as A, table2 as B
    WHERE .....

    I am using Informix 10, then this behavior is not supported, I think that I can use a subquery:

    SELECT COUNT(*)
    FROM (SELECT COUNT(A.id, B.id, ...)
    FROM table1 as A, table2 as B
    WHERE .....) as D

    But is unsupported too in Informix 10 (in Mysql the two approaches are supported)

    Some ideas?

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Code:
    SELECT count(*) FROM table_a a, table_b b WHERE a.pkey = b.fkey ...
    With a regular join the count function returns the number of rows that satisfy the conditions. But this is very straightforward, can't imagine MySql needs an alternate construction for that, do you need a count of rows involved in an outer join perhaps? Then you need to use something like:
    Code:
    SELECT count(a.pkey), count(b.pkey) FROM table_a a, OUTER table_b b 
    WHERE a.pkey = b.fkey ...
    Regards

  3. #3
    Join Date
    Sep 2007
    Posts
    4

    Smile I have found a solution

    Tyveleyn, some times a DISTINCT clause can filter the results (the sql generator generates DISCTINT if the user ask for it), this is the reason I need to count by several columns, I have found a solution using multiset:

    select count(*) as rownum
    from table(multiset(SELECT DISTINCT.....)) s1;

    Then I can get the number of rows of any sql query using a multiset, or I am wrong, there should be any restriction in make a subquery for a multiset?
    Last edited by soujiro; 09-06-07 at 12:50.

  4. #4
    Join Date
    Sep 2007
    Posts
    4

    Unhappy The solution is very slow

    As I wrote, I have found a solution for my problem, this is using TABLE and MULTISET:

    SELECT COUNT(*)
    FROM TABLE(MULTISET(SELECT DISTINCT a,b,c .....))

    But this solution is very slow, the subquery is not a very optimal approach, some ideas to improve the solution?

    Regards

  5. #5
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    I don't know if the MULTISET datatype is appropriate in your case. It's a datatype for a collection column, that stores multiple values in one attribute and makes the table a kind of multidimensional.
    What else you could do is perform your task with subqueries, like:
    Code:
    SELECT DISTINCT a.some_column,
    	(SELECT count(*) FROM table1 b
    	 WHERE b.some_column = a.some_column),
    	(SELECT count(*) FROM table2 c
    	 WHERE c.foreign_key = a.primary_key)
    FROM table1 a
    In this case the count of the rows in the subqueries are independent from the number of results in the main query.

    Regards

  6. #6
    Join Date
    Sep 2007
    Posts
    4

    The problem

    I think that I have not explained successfully the problem, I need to get the rows of a generated query, e.g. the query may be:

    select DISTINCT companyid, employeeid from report

    Then I need to get the rows generated by this query, a query like:

    select COUNT(DISTINCT companyid, employeeid) from report

    But this don´t works in Informix 10, in mysql this executes without problems.

    I have found a solution with MULTISET:

    select COUNT(*)
    from TABLE(MULTISET(select DISTINCT companyid, employeeid from report))

    But this query is really slow. Some ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •