1. Registered User
Join Date
Jul 2010
Posts
6

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!!

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
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

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
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?

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?

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
3) Looking into your sample result,
there is a one-to-one relationship between Symbol and ULV.
Is it right?

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.```

#### Posting Permissions

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