Link to home
Create AccountLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

Combine seperate queries into one

I have four seperate queries that I would like to combine into one.
I would like for the query to return only one
SELECT UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm,
EX_REV, GR_REV, INT_REV, FR_REV

Can anyone help?
Four-Queries.txt
ASKER CERTIFIED SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If needed, you could create a view and query the view which would make your code much easier to read.
Avatar of Euro5

ASKER

Lionking, This does work, but it gives me a line for each query. I wonder if I can combine these so that I get one line for each month with all the revenue.

For instance, now I get:
acct  mmyyy  ex rev     gr rev      int rev      fr rev
1      201209   50.00
1      201209                100.00

What I would like to get:
acct  mmyyy  ex rev     gr rev      int rev      fr rev
1      201209   50.00    100.00
Yes, you can put the previous query in the from clause of another query and sum your columns.
Something like:

SELECT accts, rev_dt_yyyymm, SUM(EX_REV) AS EX_REV, SUM(GR_REV) AS GR_REV, 
SUM(INT_REV) AS INT_REV, SUM(FR_REV) AS FR_REV
FROM (
SELECT UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm, SUM(net_rev_amt) AS EX_REV, NULL AS GR_REV, NULL AS INT_REV, NULL AS FR_REV
FROM  UI_RESULTS_DB.FY13Q2, CRP_REV_SUMRY
WHERE shpr_cust_acct_nbr = cast(UI_RESULTS_DB.FY13Q2.accts as varchar(20)) 
AND rev_dt_yyyymm = '201209'
AND rev_src_cd = 'DOM'
AND shpr_cust_acct_type_cd = 'FX'
GROUP BY UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm
UNION ALL
SELECT UI_RESULTS_DB.FY13Q2.accts, CRP_FXGND_FXSP_REV_SUMRY.rev_dt_yyyymm, NULL AS EX_REV, SUM(net_rev_amt) AS GR_REV, NULL AS INT_REV, NULL AS FR_REV
FROM  UI_RESULTS_DB.FY13Q2, CRP_FXGND_FXSP_REV_SUMRY, CRP_FXGND_FX_ACCOUNT_XREF
WHERE fx_cust_acct_nbr = cast(UI_RESULTS_DB.FY13Q2.accts as varchar(20)) 
AND shpr_cust_acct_nbr = fxgnd_cust_acct_nbr
AND CRP_FXGND_FXSP_REV_SUMRY.rev_dt_yyyymm = '201209'
GROUP BY UI_RESULTS_DB.FY13Q2.accts, CRP_FXGND_FXSP_REV_SUMRY.rev_dt_yyyymm
UNION ALL
SELECT UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm, NULL AS EX_REV, NULL AS GR_REV, SUM(net_rev_amt) AS INT_REV, NULL AS FR_REV
FROM  UI_RESULTS_DB.FY13Q2, CRP_REV_SUMRY
WHERE shpr_cust_acct_nbr = cast(UI_RESULTS_DB.FY13Q2.accts as varchar(20)) 
AND rev_dt_yyyymm = '201209'
AND rev_src_cd = 'INTL'
AND shpr_cust_acct_type_cd = 'FX'
GROUP BY UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm
UNION ALL
SELECT UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm, NULL AS EX_REV, NULL AS GR_REV, NULL AS INT_REV, SUM(net_rev_amt) AS FR_REV
FROM  UI_RESULTS_DB.FY13Q2, CRP_REV_SUMRY
WHERE shpr_cust_acct_nbr = cast(UI_RESULTS_DB.FY13Q2.accts as varchar(20)) 
AND rev_dt_yyyymm = '201109'
AND rev_src_cd = 'FDFR'
GROUP BY UI_RESULTS_DB.FY13Q2.accts, rev_dt_yyyymm) myTable
GROUP BY accts, rev_dt_yyyymm

Open in new window


I'm not pretty sure about your first column name, so you might want to check that in case it's giving any errors.

Let us know if this works.