asked on

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

slightwv (䄆 Netminder)

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

thanks that make better sense.