INFORMIX: a count of each elementname from sub-query of distinct callid and the Max varable value

Question

Here are my tables:

I need to get a count of the elementname for each each callid where the varname equals 'eCounter' and the varvalue is the greatest.

The tables are relational by elementid one (element) to many (elementdetail). I include the format of the actual elementid so you don't assume they are consecutive numbers.

I tried the following code just to get the Max varvalue/elementid for each call, but that did not work out either.

SELECT MAX(a.varvalue) as MAXvarvalue, b.callgui FROM elementdetail AS a, element AS b WHERE (a.elementid = b.elementid) AND (a.varname = 'eCounter') GROUP BY varname, callguid

This is what I need the results to look like.

Answer 1

Using the latest Informix version ( 14.10.XC1 ).

Assuming the following tables:

CREATE TABLE element
(
    callid      INTEGER,
    elementid   BIGINT,
    elementname CHAR( 20 )
);

CREATE TABLE elementdetail
(
    elementid   BIGINT,
    varname     CHAR( 20 ),
    varvalue    INTEGER
);

Which are populated with your sample data ( OCR to the rescue, you really should post your sample data and tables as text, not images ):

INSERT INTO element VALUES ( 1, 1001901560322810000, 'set_Page1' );
INSERT INTO element VALUES ( 1, 1001921560322810000, 'set_Page5' );
INSERT INTO element VALUES ( 1, 1001181560322820000, 'set_Page4' );
INSERT INTO element VALUES ( 1, 1001021560322820000, 'set_Page3' );
INSERT INTO element VALUES ( 1, 1001331560322830000, 'set_Page6' );
INSERT INTO element VALUES ( 2, 1002281560322920000, 'set_Page1' );
INSERT INTO element VALUES ( 2, 1002301560322920000, 'set_Page5' );
INSERT INTO element VALUES ( 2, 1002881560322940000, 'set_Page4' );
INSERT INTO element VALUES ( 3, 1002271560322950000, 'set_Page1' );
INSERT INTO element VALUES ( 3, 1002951560322970000, 'set_Page4' );
INSERT INTO element VALUES ( 3, 1002231560322980000, 'set_Page6' );
INSERT INTO element VALUES ( 4, 1002781560323000000, 'set_Page1' );
INSERT INTO element VALUES ( 4, 1002891560323020000, 'set_Page5' );
INSERT INTO element VALUES ( 4, 1002391560323040000, 'set_Page4' );

INSERT INTO elementdetail VALUES ( 1001901560322810000, 'eCounter', 0 );
INSERT INTO elementdetail VALUES ( 1001901560322810000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1001921560322810000, 'eCounter', 1 );
INSERT INTO elementdetail VALUES ( 1001921560322810000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1001181560322820000, 'eCounter', 2 );
INSERT INTO elementdetail VALUES ( 1001181560322820000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1001021560322820000, 'eCounter', 4 );
INSERT INTO elementdetail VALUES ( 1001021560322820000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1001331560322830000, 'eCounter', 5 );
INSERT INTO elementdetail VALUES ( 1001331560322830000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002281560322920000, 'eCounter', 0 );
INSERT INTO elementdetail VALUES ( 1002281560322920000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002301560322920000, 'eCounter', 1 );
INSERT INTO elementdetail VALUES ( 1002301560322920000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002881560322940000, 'eCounter', 2 );
INSERT INTO elementdetail VALUES ( 1002881560322940000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002271560322950000, 'eCounter', 0 );
INSERT INTO elementdetail VALUES ( 1002271560322950000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002951560322970000, 'eCounter', 1 );
INSERT INTO elementdetail VALUES ( 1002951560322970000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002231560322980000, 'eCounter', 2 );
INSERT INTO elementdetail VALUES ( 1002231560322980000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002781560323000000, 'eCounter', 0 );
INSERT INTO elementdetail VALUES ( 1002781560323000000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002891560323020000, 'eCounter', 1 );
INSERT INTO elementdetail VALUES ( 1002891560323020000, 'other_variables', NULL );
INSERT INTO elementdetail VALUES ( 1002391560323040000, 'eCounter', 2 );
INSERT INTO elementdetail VALUES ( 1002391560323040000, 'other_variables', NULL );

We can do something like this, making use of a CTE:

WITH cte_element AS
(
    SELECT
        e.callid,
        e.elementname,
        d.varvalue
    FROM
        element AS e
    INNER JOIN
        elementdetail AS d
    ON
        e.elementid = d.elementid
    WHERE
        d.varname = 'eCounter'
    ORDER BY
        e.callid,
        d.varvalue
) 
SELECT
    vt1.elementname,
    COUNT( vt1.elementname ) AS count_elementname
FROM
(
    SELECT
        tmp1.callid,
        tmp1.elementname,
        tmp1.varvalue
    FROM
        cte_element AS tmp1
    WHERE
        tmp1.varvalue = 
        (
            SELECT 
                MAX( tmp2.varvalue )
            FROM
                cte_element AS tmp2
            WHERE
                tmp1.callid = tmp2.callid
            GROUP BY
                tmp2.callid
        )
) AS vt1
GROUP BY
    vt1.elementname
;

-- Results
elementname          count_elementname

set_Page6                            2
set_Page4                            2

Still using a a recent version of Informix, using windowing instead of a CTE:

SELECT
    vt1.elementname,
    COUNT( vt1.elementname ) AS count_elementname
FROM
(
SELECT
    e.callid,
    e.elementname,
    d.varvalue,
    RANK() OVER 
    ( 
        PARTITION BY e.callid ORDER BY e.callid, d.varvalue DESC 
    ) AS rank
FROM
    element AS e
INNER JOIN
    elementdetail AS d
ON
    e.elementid = d.elementid
WHERE
    d.varname = 'eCounter'
ORDER BY
    e.callid,
    d.varvalue
) AS vt1
WHERE
    vt1.rank = 1
GROUP BY
    vt1.elementname
;

-- Results
elementname          count_elementname

set_Page6                            2
set_Page4                            2

With an older Informix version, without CTE's or windowing, so we use a temporary table:

SELECT
    e.callid,
    e.elementname,
    d.varvalue
FROM
    element AS e
INNER JOIN
    elementdetail AS d
ON
    e.elementid = d.elementid
WHERE
    d.varname = 'eCounter'
ORDER BY
    e.callid,
    d.varvalue
INTO TEMP temp_element 
;

SELECT
    vt1.elementname,
    COUNT( vt1.elementname ) AS count_elementname
FROM
(
    SELECT
        tmp1.callid,
        tmp1.elementname,
        tmp1.varvalue
    FROM
        temp_element AS tmp1
    WHERE
        tmp1.varvalue = 
        (
            SELECT 
                MAX( tmp2.varvalue )
            FROM
                temp_element AS tmp2
            WHERE
                tmp1.callid = tmp2.callid
            GROUP BY
                tmp2.callid
        )
) AS vt1
GROUP BY
    vt1.elementname
;

-- Results
elementname          count_elementname

set_Page6                            2
set_Page4                            2

I have no idea of how any of this queries will scale when you use a more robust data set ( hundred thousands or millions of rows on each table ).

I have not tested any corner case, for example, when a callid has 2 elementname with the same eCounter varvalue values.