Sum values for two different category fields

Need to determine the sum values of two groups of Categories.  with 1 sum field.  How do I get the sum for Cat1 and also the Sum for cat2 in the same query.  OR do I need to create a union query?

SELECT A.BUDGET_YEAR 
      , A.BUDGET_MONTH
      , A.CATEGORY
      , A.CAT1_TOTALS
      , B.CATEGORY2
      , B.CAT2_TOTALS
FROM
    (
      SELECT
           BUDGET_MONTH
        ,  BUDGET_YEAR
        ,  SSGSM_TAXONOMY_LEVEL1    CATEGORY1
        ,  SUM(SPEND_USD)           CAT1_TOTALS
      FROM
          table1
      WHERE
          BUDGET_YEAR = '2017'
      GROUP BY 
             BUDGET_MONTH
             , SSGSM_TAXONOMY_LEVEL1
    UNION ALL
      SELECT
           BUDGET_MONTH
        ,  SSGSM_TAXONOMY_LEVEL2    CATEGORY2
        ,  SUM(SPEND_USD)           CAT2_TOTALS
      FROM
          table1 
      WHERE
          BUDGET_YEAR = '2017'
      GROUP BY 
             BUDGET_MONTH
             , SSGSM_TAXONOMY_LEVEL2
    );

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>> OR do I need to create a union query?

You have a UNION query.  That is what the UNION ALL gives you.

The query you posted won't run.   In a UNION query ALL the queries need the same number of columns.  You also don't have an A or B tables so you cannot reference them in the outer query.

I'm not seeing the need for a UNION at all since you are accessing the same table.

If you can provide some sample data and expected results I think we can get you what you want only accessing the table once.
0
slightwv (䄆 Netminder) Commented:
Depending on the rules between SSGSM_TAXONOMY_LEVEL1 and SSGSM_TAXONOMY_LEVEL2 it might go something like the code below.

To get the SUM I think you want, you need some way to decide which SSGSM_TAXONOMY_LEVEL goes with which SPEND_USD.  Once you figure that out, the rest is easy.

If the rules go:  If one is populated, the other is null then try the code below.  I cannot test it since I don't have your tables or data but it should give you the basic idea.

      SELECT
           BUDGET_MONTH
        ,  BUDGET_YEAR
        ,  nvl(SSGSM_TAXONOMY_LEVEL1,SSGSM_TAXONOMY_LEVEL2) CATEGORY
        ,  SUM(case when SSGSM_TAXONOMY_LEVEL1 is not null then SPEND_USD end) CAT1_TOTALS,
        ,  SUM(case when SSGSM_TAXONOMY_LEVEL2 is not null then SPEND_USD end) CAT2_TOTALS
	FROM
          table1
      WHERE
          BUDGET_YEAR = '2017'
      GROUP BY 
             BUDGET_MONTH
        	,  BUDGET_YEAR
        	,  nvl(SSGSM_TAXONOMY_LEVEL1,SSGSM_TAXONOMY_LEVEL2) CATEGORY

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that make better sense.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.