Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2012
    Posts
    9

    Unanswered: Arithmetic in GROUP by

    I'm struggling to get some maths working in a group by. Basically I want
    to calculate the total cost of deployments in a country but with a varying
    cost.

    Here are an example two tables I have;

    Licenses gives me the unit cost per license:

    Table licenses
    --------------

    licensenum cost
    1048 75
    1032 100

    Deployments tells me how many deployments I have per license per country:

    Table deployments
    -----------------

    licensenum nodes country
    1048 1 UK
    1048 1 UK
    1048 3 France
    1048 3 Japan
    1048 4 Japan
    1032 5 France
    1032 2 France
    1032 3 France
    1032 5 UK
    1032 6 UK


    What I want is one single line per country giving a total calculated cost
    per country.

    So, ignoring the calculation, but showing it for clarity of the end result,
    I want something like:

    Required output
    ---------------

    Country Cost of deployments
    UK (75*(1+1)) + (100*(5+6)) = 1250
    France (75*3) + (100*(5+2+3)) = 1225
    Japan (75*(3+4)) = 525

    This is the SQL I have so far:

    select deployments.country, sum(deployments.nodes) * licenses.cost
    from deployments
    join licenses on licenses.licensenum = deployments.licensenum
    group by deployments.country, license.cost

    However this results in a seperate row per country for each individually
    seperate license cost.

    Is a FOR LOOP the only way to tackle this?

    Many Thanks
    Harry

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      licenses 
    (licensenum , cost) AS (
    VALUES
      ( 1048 ,  75 )
    , ( 1032 , 100 )
    )
    , deployments
    (licensenum , nodes , country) AS (
    VALUES
      ( 1048 , 1 , 'UK'     )
    , ( 1048 , 1 , 'UK'     )
    , ( 1048 , 3 , 'France' )
    , ( 1048 , 3 , 'Japan'  )
    , ( 1048 , 4 , 'Japan'  )
    , ( 1032 , 5 , 'France' )
    , ( 1032 , 2 , 'France' )
    , ( 1032 , 3 , 'France' )
    , ( 1032 , 5 , 'UK'     )
    , ( 1032 , 6 , 'UK'     )
    )
    SELECT country
         , SUM(cost * nodes) AS cost_of_deployments 
     FROM  deployments AS d
     INNER JOIN
           licenses    AS l
      ON   l.licensenum = d.licensenum
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;
    ------------------------------------------------------------------------------
    
    COUNTRY COST_OF_DEPLOYMENTS
    ------- -------------------
    UK                     1250
    France                 1225
    Japan                   525
    
      3 record(s) selected.
    Last edited by tonkuma; 02-16-12 at 07:04. Reason: Adjust blanks in sample code.

  3. #3
    Join Date
    Feb 2012
    Posts
    9
    Argh! Of course!

    Tonkuma thank you VERY much for your reply, it's so simple and I've just not thought to try it...

    MANY THANKS!

  4. #4
    Join Date
    Feb 2012
    Posts
    9
    I also need to include another sum in my calculation which I thought could be fixed by the above, but it seems not.
    I also have a users table and I need to multiply the cost by number of users per country.
    However I end up getting a SQLCODE=-112,SQLSTATE=42607

    Code:
    WITH
      licenses 
    (licensenum , cost) AS (
    VALUES
      ( 1048 ,  75 )
    , ( 1032 , 100 )
    )
    , deployments
    (licensenum , nodes , country) AS (
    VALUES
      ( 1048 , 1 , 'UK'     )
    , ( 1048 , 1 , 'UK'     )
    , ( 1048 , 3 , 'France' )
    , ( 1048 , 3 , 'Japan'  )
    , ( 1048 , 4 , 'Japan'  )
    , ( 1032 , 5 , 'France' )
    , ( 1032 , 2 , 'France' )
    , ( 1032 , 3 , 'France' )
    , ( 1032 , 5 , 'UK'     )
    , ( 1032 , 6 , 'UK'     )
    ), users
    (country, username) as
    VALUES
    ,('UK','mario')
    ,('UK','stefano')
    ,('UK','bob')
    ,('France','andrew')
    ,('France','wayne')
    ,('France','tom')
    ,('Japan','ed')
    ,('Japan','billy')
    ,('Japan','dave')
    ,('Japan','fumio')
    SELECT country
         , SUM(cost * nodes) AS cost_of_deployments
         , SUM(cost * count(users)) as cost_by_users
     FROM  deployments AS d
     INNER JOIN
           licenses    AS l
      ON   l.licensenum = d.licensenum
     INNER JOIN 
    	users      AS u
      on   u.country = d.country
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Which cost do you want to take?
    For example, which of cost 75 or 100 do you want to take for ('UK' , 'mario')?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Corrections must be necessary for your additional data.

    Code:
    , users
    (country, username) as (
    VALUES
    /*,*/('UK','mario')
    ,('UK','stefano')
    ,('UK','bob')
    ,('France','andrew')
    ,('France','wayne')
    ,('France','tom')
    ,('Japan','ed')
    ,('Japan','billy')
    ,('Japan','dave')
    ,('Japan','fumio')
    )
    SELECT ...

  7. #7
    Join Date
    Feb 2012
    Posts
    9
    Apologies, please try this:


    Code:
    WITH
      licenses 
    (licensenum , cost) AS (
    VALUES
      ( 1048 ,  75 )
    , ( 1032 , 100 )
    )
    , deployments
    (licensenum , nodes , country) AS (
    VALUES
      ( 1048 , 1 , 'UK'     )
    , ( 1048 , 1 , 'UK'     )
    , ( 1048 , 3 , 'France' )
    , ( 1048 , 3 , 'Japan'  )
    , ( 1048 , 4 , 'Japan'  )
    , ( 1032 , 5 , 'France' )
    , ( 1032 , 2 , 'France' )
    , ( 1032 , 3 , 'France' )
    , ( 1032 , 5 , 'UK'     )
    , ( 1032 , 6 , 'UK'     )
    ), users
    (country, username) as (
    VALUES
    (1048,'UK','mario')
    ,(1032,'UK','stefano')
    ,(1048,'UK','bob')
    ,(1032,'France','andrew')
    ,(1048,'France','wayne')
    ,(1032,'France','tom')
    ,(1048,'Japan','ed')
    ,(1032,'Japan','billy')
    ,(1032,'Japan','dave')
    ,(1048,'Japan','fumio')
    )
    SELECT country
         , SUM(cost * nodes) AS cost_of_deployments
         , (CASE when u.country = d.country 
    	THEN
    		sum(cost * count(users))
    	ELSE 0
    	END) as cost_by_users
     FROM  deployments AS d
     INNER JOIN
           licenses    AS l
      ON   l.licensenum = d.licensenum
     INNER JOIN 
    	users      AS u
      on   u.licensenum = d.licensenum
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;
    Last edited by niavasha; 02-16-12 at 08:26. Reason: Missed off brackets around user table

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It must be better to add a column name "licensenum" to a table "users".


    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      licenses 
    (licensenum , cost) AS (
    VALUES
      ( 1048 ,  75 )
    , ( 1032 , 100 )
    )
    , deployments
    (licensenum , nodes , country) AS (
    VALUES
      ( 1048 , 1 , 'UK'     )
    , ( 1048 , 1 , 'UK'     )
    , ( 1048 , 3 , 'France' )
    , ( 1048 , 3 , 'Japan'  )
    , ( 1048 , 4 , 'Japan'  )
    , ( 1032 , 5 , 'France' )
    , ( 1032 , 2 , 'France' )
    , ( 1032 , 3 , 'France' )
    , ( 1032 , 5 , 'UK'     )
    , ( 1032 , 6 , 'UK'     )
    )
    , users
    (licensenum , country , username) AS (
    VALUES
      ( 1048 , 'UK'     , 'mario'   )
    , ( 1032 , 'UK'     , 'stefano' )
    , ( 1048 , 'UK'     , 'bob'     )
    , ( 1032 , 'France' , 'andrew'  )
    , ( 1048 , 'France' , 'wayne'   )
    , ( 1032 , 'France' , 'tom'     )
    , ( 1048 , 'Japan'  , 'ed'      )
    , ( 1032 , 'Japan'  , 'billy'   )
    , ( 1032 , 'Japan'  , 'dave'    )
    , ( 1048 , 'Japan'  , 'fumio'   )
    )
    SELECT d.country
         , cost_of_deployments
         , cost_by_users
     FROM  (SELECT d.country
                 , SUM(cost * nodes) AS cost_of_deployments
             FROM  deployments AS d
             INNER JOIN
                   licenses    AS l
              ON   l.licensenum = d.licensenum
             GROUP BY
                   d.country
           ) d
     INNER JOIN
           (SELECT u.country
                 , SUM(cost)         AS cost_by_users
             FROM  users       AS u
             INNER JOIN
                   licenses    AS l
              ON   l.licensenum = u.licensenum
             GROUP BY
                   u.country
           ) u
      ON   u.country = d.country
     ORDER BY
           CASE d.country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;
    ------------------------------------------------------------------------------
    
    COUNTRY COST_OF_DEPLOYMENTS COST_BY_USERS
    ------- ------------------- -------------
    UK                     1250           250
    France                 1225           275
    Japan                   525           350
    
      3 record(s) selected.
    Note: Assumed all countries in deployments also were in users, and the reverse too.
    If some countries in deployments were not in users, or some countries in users were not in deployments,
    then OUTER JOIN should be used.
    Last edited by tonkuma; 02-16-12 at 09:01.

  9. #9
    Join Date
    Feb 2012
    Posts
    9
    Domo arigato Tonkuma-san for all your help.

    Best

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 2 refers licenses table twice.

    The following example refers each tables once.

    Example 3:
    Code:
    SELECT COALESCE(u.country , d.country) AS country
         , SUM(cost * nodes) AS cost_of_deployments 
         , SUM(cost * users) AS cost_by_users
     FROM  licenses    AS l
     INNER JOIN
           (SELECT country
                 , licensenum
                 , SUM(nodes)  AS nodes
             FROM  deployments AS d
             GROUP BY
                   country
                 , licensenum
           ) d
     FULL  OUTER JOIN
           (SELECT country
                 , licensenum
                 , COUNT(*)    AS users
             FROM  users       AS u
             GROUP BY
                   country
                 , licensenum
           ) u
      ON   u.licensenum = d.licensenum
       AND u.country    = d.country
      ON   l.licensenum = COALESCE(u.licensenum , d.licensenum)
     GROUP BY
           COALESCE(u.country , d.country)
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Simpler than previous examples
    and (perhaps) more efficient because of less join operations(one cross join) and grouping once.

    Example 4:
    Code:
    SELECT country
         , SUM(cost * CASE tbl_id WHEN 'd' THEN number END) AS cost_of_deployments 
         , SUM(cost * CASE tbl_id WHEN 'u' THEN number END) AS cost_by_users
     FROM  licenses    AS l
     CROSS JOIN
      LATERAL
           (
           SELECT country
                , nodes
                , 'd'
            FROM  deployments AS d
            WHERE d.licensenum = l.licensenum
           UNION ALL
           SELECT country
                , 1
                , 'u'
            FROM  users       AS u
            WHERE u.licensenum = l.licensenum
           ) f(country , number , tbl_id)
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

    Example 5:
    Code:
    SELECT country
         , SUM(cost * CASE tbl_id WHEN 'd' THEN number END) AS cost_of_deployments 
         , SUM(cost * CASE tbl_id WHEN 'u' THEN number END) AS cost_by_users
     FROM  licenses    AS l
     INNER JOIN
           (
           SELECT country
                , licensenum
                , nodes
                , 'd'
            FROM  deployments AS d
           UNION ALL
           SELECT country
                , licensenum
                , 1
                , 'u'
            FROM  users       AS u
           ) f(country , licensenum , number , tbl_id)
       ON  f.licensenum = l.licensenum
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;
    Last edited by tonkuma; 02-17-12 at 02:27. Reason: Add Example 5.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 6 and Example 7 are variations of Example 4 and Example 5, but a little shorter.

    Although the costs reported by Explain were about same,
    access paths were quite different.
    Access path and cost:
    (Example 4 = Example 6) <> (Example 5 = Example 7)

    I felt that Example 6 was near the best in my examples with a test data,
    considering simplicity of SQL statement and its cost.
    Though, access path, cost and execution time would be different by
    table size(number of rows, number of columns of the tables) and indexes, so on...


    Example 6:
    Code:
    SELECT country
         , SUM(cost * nodes) AS cost_of_deployments 
         , SUM(cost * users) AS cost_by_users
     FROM  licenses    AS l
     CROSS JOIN
      LATERAL(
           SELECT country
                , nodes
                , 0
            FROM  deployments AS d
            WHERE d.licensenum = l.licensenum
           UNION ALL
           SELECT country
                , 0
                , 1
            FROM  users       AS u
            WHERE u.licensenum = l.licensenum
           ) f(country , nodes , users)
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

    Example 7:
    Code:
    SELECT country
         , SUM(cost * nodes) AS cost_of_deployments 
         , SUM(cost * users) AS cost_by_users
     FROM  licenses    AS l
     INNER JOIN
           (
           SELECT country
                , licensenum
                , nodes
                , 0
            FROM  deployments
           UNION  ALL
           SELECT country
                , licensenum
                , 0
                , 1
            FROM  users
           ) f(country , licensenum , nodes , users)
       ON  f.licensenum = l.licensenum
     GROUP BY
           country
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 6-e: Extracted from db2exfmt
    Code:
    Original Statement:
    ------------------
    WITH licenses (licensenum , cost) AS
           (VALUES (1048 , 75) , (1032 , 100))
            , deployments (licensenum , nodes , country) AS (VALUES (1048 , 1 ,
            'UK') , (1048 , 1 , 'UK') , (1048 , 3 , 'France') , (1048 , 3 ,
            'Japan') , (1048 , 4 , 'Japan') , (1032 , 5 , 'France') , (1032 , 2 ,
            'France') , (1032 , 3 , 'France') , (1032 , 5 , 'UK') , (1032 , 6 ,
            'UK')) , users (licensenum , country , username) AS (VALUES (1048 ,
            'UK' , 'mario') , (1032 , 'UK' , 'stefano') , (1048 , 'UK' , 'bob') ,
            (1032 , 'France' , 'andrew') , (1048 , 'France' , 'wayne') , (1032 ,
            'France' , 'tom') , (1048 , 'Japan' , 'ed') , (1032 , 'Japan' ,
            'billy') , (1032 , 'Japan' , 'dave') , (1048 , 'Japan' , 'fumio'))
               SELECT country , SUM(cost * nodes) AS cost_of_deployments ,
               SUM(cost * users) AS cost_by_users
       FROM licenses AS l CROSS JOIN LATERAL
          (SELECT country , nodes , 0
          FROM deployments AS d
          WHERE d.licensenum = l.licensenum
          UNION ALL
          SELECT country , 0 , 1
          FROM users AS u
          WHERE u.licensenum = l.licensenum) f(country , nodes , users)
       GROUP BY country
       ORDER BY
       CASE country
       WHEN 'UK'
       THEN 1
       WHEN 'France'
       THEN 2
       WHEN 'Japan'
       THEN 3
       ELSE 999 END
    
    
    Optimized Statement:
    -------------------
    SELECT Q12.$C0 AS "COUNTRY", Q12.$C1 AS "COST_OF_DEPLOYMENTS", Q12.$C2 AS
            "COST_BY_USERS", Q12.$C3
    FROM
       (SELECT Q11.$C0, Q11.$C1, Q11.$C2,
       CASE
       WHEN (Q11.$C0 = 'UK')
       THEN 1
       WHEN (Q11.$C0 = 'France')
       THEN 2
       WHEN (Q11.$C0 = 'Japan')
       THEN 3
       ELSE 999 END
       FROM
          (SELECT Q10.$C0, SUM((Q10.$C1 * Q10.$C3)), SUM((Q10.$C1 * Q10.$C2))
          FROM
             (SELECT Q7.$C0, Q9.$C1, Q7.$C2, Q7.$C1
             FROM
                (SELECT Q2.$C1, 0, 1
                FROM
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$(),
                           $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AS Q1) AS Q2
                WHERE (Q2.$C0 = Q9.$C0)
                UNION ALL
                SELECT Q5.$C2, Q5.$C1, 0
                FROM
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$(),
                           $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AS Q4) AS Q5
                WHERE (Q5.$C0 = Q9.$C0)) AS Q7,
                (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$()
                FROM (VALUES 1, 2) AS Q8) AS Q9) AS Q10
          GROUP BY Q10.$C0) AS Q11) AS Q12
    ORDER BY Q12.$C3
    
    Access Plan:
    -----------
            Total Cost:             0.0411754
            Query Degree:           1
    
                       Rows
                      RETURN
                      (   1)
                       Cost
                        I/O
                        |
                        1.2
                      TBSCAN
                      (   2)
                     0.0411754
                         0
                        |
                        1.2
                      SORT
                      (   3)
                     0.0398739
                         0
                        |
                        1.2
                      GRPBY
                      (   4)
                     0.0360677
                         0
                        |
                        1.2
                      TBSCAN
                      (   5)
                     0.0356781
                         0
                        |
                        1.2
                      SORT
                      (   6)
                     0.0343765
                         0
                        |
                         4
                      NLJOIN
                      (   7)
                     0.0305335
                         0
              /---------+---------\
             2                       2
          TBSCAN                  UNION
          (   8)                  (   9)
        0.000106277              0.0137948
             0                       0
            |                /------+------\
             2              1                 1
     TABFNC: SYSIBM      FILTER            FILTER
          GENROW         (  10)            (  12)
            Q8         0.00689741        0.00689741
                            0                 0
                           |                 |
                           10                10
                         TBSCAN            TBSCAN
                         (  11)            (  13)
                       0.000276321       0.000276321
                            0                 0
                           |                 |
                           10                10
                    TABFNC: SYSIBM    TABFNC: SYSIBM
                         GENROW            GENROW
                           Q1                Q4

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 7-e: Extracted from db2exfmt
    Code:
    Original Statement:
    ------------------
    WITH licenses (licensenum , cost) AS
           (VALUES (1048 , 75) , (1032 , 100))
            , deployments (licensenum , nodes , country) AS (VALUES (1048 , 1 ,
            'UK') , (1048 , 1 , 'UK') , (1048 , 3 , 'France') , (1048 , 3 ,
            'Japan') , (1048 , 4 , 'Japan') , (1032 , 5 , 'France') , (1032 , 2 ,
            'France') , (1032 , 3 , 'France') , (1032 , 5 , 'UK') , (1032 , 6 ,
            'UK')) , users (licensenum , country , username) AS (VALUES (1048 ,
            'UK' , 'mario') , (1032 , 'UK' , 'stefano') , (1048 , 'UK' , 'bob') ,
            (1032 , 'France' , 'andrew') , (1048 , 'France' , 'wayne') , (1032 ,
            'France' , 'tom') , (1048 , 'Japan' , 'ed') , (1032 , 'Japan' ,
            'billy') , (1032 , 'Japan' , 'dave') , (1048 , 'Japan' , 'fumio'))
               SELECT country , SUM(cost * nodes) AS cost_of_deployments ,
               SUM(cost * users) AS cost_by_users
       FROM licenses AS l INNER JOIN
          (SELECT country , licensenum , nodes , 0
          FROM deployments
          UNION ALL
          SELECT country , licensenum , 0 , 1
          FROM users) f(country , licensenum , nodes , users) ON f.licensenum =
               l.licensenum
       GROUP BY country
       ORDER BY
       CASE country
       WHEN 'UK'
       THEN 1
       WHEN 'France'
       THEN 2
       WHEN 'Japan'
       THEN 3
       ELSE 999 END
    
    
    Optimized Statement:
    -------------------
    SELECT Q15.$C0 AS "COUNTRY", Q15.$C1 AS "COST_OF_DEPLOYMENTS", Q15.$C2 AS
            "COST_BY_USERS", Q15.$C3
    FROM
       (SELECT Q14.$C0, Q14.$C1, Q14.$C2,
       CASE
       WHEN (Q14.$C0 = 'UK')
       THEN 1
       WHEN (Q14.$C0 = 'France')
       THEN 2
       WHEN (Q14.$C0 = 'Japan')
       THEN 3
       ELSE 999 END
       FROM
          (SELECT Q13.$C0, SUM(Q13.$C2), SUM(Q13.$C1)
          FROM
             (SELECT Q5.$C0, SUM((Q5.$C1 * 1)), SUM((Q5.$C1 * 0))
             FROM
                (SELECT Q4.$C1, Q2.$C1
                FROM
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2) AS Q1) AS Q2,
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$(),
                           $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AS Q3) AS Q4
                WHERE (Q4.$C0 = Q2.$C0)) AS Q5
             GROUP BY Q5.$C0
             UNION ALL
             SELECT Q11.$C0, SUM((Q11.$C1 * 0)), SUM((Q11.$C1 * Q11.$C2))
             FROM
                (SELECT Q10.$C2, Q8.$C1, Q10.$C1
                FROM
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2) AS Q7) AS Q8,
                   (SELECT $INTERNAL_FUNC$(), $INTERNAL_FUNC$(),
                           $INTERNAL_FUNC$()
                   FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AS Q9) AS Q10
                WHERE (Q10.$C0 = Q8.$C0)) AS Q11
             GROUP BY Q11.$C0) AS Q13
          GROUP BY Q13.$C0) AS Q14) AS Q15
    ORDER BY Q15.$C3
    
    Access Plan:
    -----------
            Total Cost:             0.0413379
            Query Degree:           1
    
                                      Rows
                                     RETURN
                                     (   1)
                                      Cost
                                       I/O
                                       |
                                        4
                                     TBSCAN
                                     (   2)
                                    0.0413379
                                        0
                                       |
                                        4
                                     SORT
                                     (   3)
                                    0.0395404
                                        0
                                       |
                                        4
                                     GRPBY
                                     (   4)
                                    0.0323915
                                        0
                                       |
                                        4
                                     TBSCAN
                                     (   5)
                                    0.0315058
                                        0
                                       |
                                        4
                                     SORT
                                     (   6)
                                    0.0297083
                                        0
                                       |
                                        4
                                     UNION
                                     (   7)
                                    0.0242577
                                        0
                       /---------------+---------------\
                      2                                   2
                   GRPBY                               GRPBY
                   (   8)                              (  14)
                  0.0111525                           0.0112134
                      0                                   0
                     |                                   |
                      2                                   2
                   TBSCAN                              TBSCAN
                   (   9)                              (  15)
                  0.0106211                           0.0106821
                      0                                   0
                     |                                   |
                      2                                   2
                   SORT                                SORT
                   (  10)                              (  16)
                 0.00917788                          0.00923881
                      0                                   0
                     |                                   |
                      2                                   2
                   HSJOIN                              HSJOIN
                   (  11)                              (  17)
                 0.00654361                          0.00654361
                      0                                   0
              /------+------\                     /------+------\
            10                 2                10                 2
          TBSCAN            TBSCAN            TBSCAN            TBSCAN
          (  12)            (  13)            (  18)            (  19)
        0.000276321       0.000106277       0.000276321       0.000106277
             0                 0                 0                 0
            |                 |                 |                 |
            10                 2                10                 2
     TABFNC: SYSIBM    TABFNC: SYSIBM    TABFNC: SYSIBM    TABFNC: SYSIBM
          GENROW            GENROW            GENROW            GENROW
            Q3                Q1                Q9                Q7

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Short in SQL code, but a little tricky.

    Example 8:
    Code:
    SELECT COALESCE(d.country , u.country) AS country
         , SUM( cost * nodes )             AS cost_of_deployments
         , SUM( cost * INT(COS(nodes)) )   AS cost_by_users
     FROM  licenses    AS l
     INNER JOIN
           (
           SELECT *
            FROM  deployments
           UNION ALL
           VALUES ( NULLIF(0 , 0) , 0 , NULLIF('' , '') )
           ) AS d(licensenum , nodes , country)
     LEFT  OUTER JOIN
           users       AS u
      ON   d.nodes = 0 /*d.licensenum IS NULL*/
      ON   l.licensenum = COALESCE(d.licensenum , u.licensenum) 
     GROUP BY
           COALESCE(d.country , u.country)
     ORDER BY
           CASE country
           WHEN 'UK'     THEN   1
           WHEN 'France' THEN   2
           WHEN 'Japan'  THEN   3
           ELSE               999
           END
    ;

Posting Permissions

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