# Thread: Arithmetic in GROUP by

1. Registered User
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;

--------------

1048 75
1032 100

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

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

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:

from deployments

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

Is a FOR LOOP the only way to tackle this?

Many Thanks
Harry

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

Example 1:
Code:
```------------------------------ Commands Entered ------------------------------
WITH
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
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. Registered User
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. Registered User
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
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
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
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. Registered User
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483

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

7. Registered User
Join Date
Feb 2012
Posts
9

Code:
```WITH
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
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
INNER JOIN
users      AS u
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. Registered User
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
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
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
GROUP BY
d.country
) d
INNER JOIN
(SELECT u.country
, SUM(cost)         AS cost_by_users
FROM  users       AS u
INNER JOIN
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. Registered User
Join Date
Feb 2012
Posts
9
Domo arigato Tonkuma-san for all your help.

Best

10. Registered User
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
INNER JOIN
(SELECT country
, SUM(nodes)  AS nodes
FROM  deployments AS d
GROUP BY
country
) d
FULL  OUTER JOIN
(SELECT country
, COUNT(*)    AS users
FROM  users       AS u
GROUP BY
country
) u
AND u.country    = d.country
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. Registered User
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
CROSS JOIN
LATERAL
(
SELECT country
, nodes
, 'd'
FROM  deployments AS d
UNION ALL
SELECT country
, 1
, 'u'
FROM  users       AS u
) 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
INNER JOIN
(
SELECT country
, nodes
, 'd'
FROM  deployments AS d
UNION ALL
SELECT country
, 1
, 'u'
FROM  users       AS u
) f(country , licensenum , 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
;```
Last edited by tonkuma; 02-17-12 at 02:27. Reason: Add Example 5.

12. Registered User
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
CROSS JOIN
LATERAL(
SELECT country
, nodes
, 0
FROM  deployments AS d
UNION ALL
SELECT country
, 0
, 1
FROM  users       AS u
) 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
INNER JOIN
(
SELECT country
, nodes
, 0
FROM  deployments
UNION  ALL
SELECT country
, 0
, 1
FROM  users
) f(country , licensenum , nodes , users)
GROUP BY
country
ORDER BY
CASE country
WHEN 'UK'     THEN   1
WHEN 'France' THEN   2
WHEN 'Japan'  THEN   3
ELSE               999
END
;```

13. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Example 6-e: Extracted from db2exfmt
Code:
```Original Statement:
------------------
(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
UNION ALL
SELECT country , 0 , 1
FROM users AS u
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Example 7-e: Extracted from db2exfmt
Code:
```Original Statement:
------------------
(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 =
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. Registered User
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
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*/
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
•