| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-10-11, 04:58
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 6
|
|
|
Subtotal in db2
|
|
I have joined 2 tables and the result of the query is as follows:
I want to create 2 subtotal of the amount. One based on code and the other based on code and ULV.
Code Symbol amount ULV
JSCC JGBLF 8750680000 BO
JSCC JGBLF 80000 BO
JSCC JGBLF 720980000 BO
JSCC JGBLF 8765880000 BO
JSCC JGBLF 721190000 BO
JSCC TOPIXF 3987710000 IX
JSCC TOPIXF 49000000 IX
My query is as follows:
select y.code, y.symbol, y.amount, u.ULV from YEAR_mon y
inner join ULV_code u on y.product = u.product
where transaction_date=1101018 and
order by y.code, y.symbol
Im getting stuck in creating a subtotal.Can someone help!!
|
|
01-10-11, 06:16
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
the first part is really simple.
Code:
select y.code, y.symbol, sum(y.amount), u.ULV
from YEAR_mon y
inner join ULV_code u
on y.product = u.product
where transaction_date=1101018
and ??? -- you left this blank
group by y.code, y.symbol, u.ULV
order by y.code, y.symbol
The above will give you your sub-total. It is more efficient to add those subtotals up in your presentation layer. Having said that, you can do it within your SQL by running the query again, in a nested expression to sum up at a different level.
Dave nance
|
|
01-10-11, 08:33
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
You can get two subtotals by specifying "GROUP BY GROUPING SETS ( code , (code , ulv) )".
by the way,
I have some questions.
1-a) Do you want to get both of joined result(which you showed) and two subtotals?
or
1-b) Your final requirements are two subtotals only, and joined result you showed was a temporary result.
2-a) If 1-a) was right.
How to order whole result?
joined result(which you showed) was ordered by code and symbol.
while subtotals you want are "based on code" and "based on code and ULV".
(symbol was not included.)
2-b) If 1-b) was right.
Can we ignore symbol in final result?
3) Looking into your sample result,
there is a one-to-one relationship between Symbol and ULV.
Is it right?
If the answer was "Yes",
3-1) "subtotal based on code and ULV" would be same as "subtotal based on code and sumbol".
Then you can get both of joined result and two subtotals ordered by code and symbol.
Do you agree this?
3-2) Is there any table having relationship between Symbol and ULV?
|
|
01-10-11, 19:50
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Quote:
3) Looking into your sample result,
there is a one-to-one relationship between Symbol and ULV.
Is it right?
If the answer was "Yes",
3-1) "subtotal based on code and ULV" would be same as "subtotal based on code and sumbol".
Then you can get both of joined result and two subtotals ordered by code and symbol.
Do you agree this?
|
Although, the following query may not be exactly what you want,
(because, you didn't show the values of product and other columns)
you would get some hints to solve your problem.
Code:
------------------------------ Commands Entered ------------------------------
WITH
YEAR_mon(code, symbol, amount, product) AS (
VALUES
('JSCC' , 'JGBLF' , 8750680000 , 1)
, ('JSCC' , 'JGBLF' , 80000 , 2)
, ('JSCC' , 'JGBLF' , 720980000 , 3)
, ('JSCC' , 'JGBLF' , 8765880000 , 4)
, ('JSCC' , 'JGBLF' , 721190000 , 5)
, ('JSCC' , 'TOPIXF' , 3987710000 , 6)
, ('JSCC' , 'TOPIXF' , 49000000 , 7)
)
, ULV_code(product, ulv) AS (
VALUES
( 1 , 'BO')
, ( 2 , 'BO')
, ( 3 , 'BO')
, ( 4 , 'BO')
, ( 5 , 'BO')
, ( 6 , 'IX')
, ( 7 , 'IX')
)
SELECT y.code
, y.symbol
, u.ulv
, CASE
WHEN GROUPING(y.symbol) = 1 THEN
'**Total**'
WHEN GROUPING(y.amount) = 1 THEN
'Sub total'
ELSE ''
END AS summary
, SUM(y.amount) AS amounts
FROM YEAR_mon y
INNER JOIN
ULV_code u
ON y.product = u.product
/*
where transaction_date=1101018
and ...
*/
GROUP BY
GROUPING SETS( y.code
, (y.code , y.symbol , u.ulv)
, (y.code , y.symbol , u.ulv , y.product , y.amount)
)
ORDER BY
y.code
, y.symbol
, GROUPING(y.product)
;
------------------------------------------------------------------------------
CODE SYMBOL ULV SUMMARY AMOUNTS
---- ------ --- --------- --------------------
JSCC JGBLF BO 8750680000
JSCC JGBLF BO 80000
JSCC JGBLF BO 720980000
JSCC JGBLF BO 8765880000
JSCC JGBLF BO 721190000
JSCC JGBLF BO Sub total 18958810000
JSCC TOPIXF IX 3987710000
JSCC TOPIXF IX 49000000
JSCC TOPIXF IX Sub total 4036710000
JSCC - - **Total** 22995520000
10 record(s) selected.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Subtotal in db2
