How to aggregate and sum values of similar selects using SQL?-CodePudding

I have some similar selects that I'd like to join and aggregate values. I've already tried with join, inner join, union, union all, without the expected result. See that the selects are very similar. The differences are: SUM(comumn_02)*.05 AS SUM_01 different from SUM(comumn_02)*.45 AS SUM_02

and AND COLUMN_05 = ALFA different from AND COLUMN_05 = BETA in the second select.

The whole thing is here:

SELECT
    COLUMN_01,
    SUM(COLUMN_02)*.05 AS SUM_01
FROM table
WHERE
    COLUMN_03 = 1
    AND COLUMN_04 = 2
    AND COLUMN_05 = ALFA
GROUP BY COLUMN_01

UNION ALL

SELECT
    COLUMN_01,
    SUM(COLUMN_02)*.45 AS SUM_02
FROM table
WHERE
    COLUMN_03 = 1
    AND COLUMN_04 = 2
    AND COLUMN_05 = BETA
GROUP BY COLUMN_01;

In this example we have the following result:

|-----------|--------|
| COLUMN_01 | SUM_01 |
|-----------|--------|
| value_01  |    465 |
| value_02  |    186 |
| value_03  |    245 |
| value_01  |    102 |
| value_02  |    108 |
| value_03  |    325 |
|--------------------|

But what I'd like to have would be:

|-----------|--------|
| COLUMN_01 | SUM_01 |
|-----------|--------|
| value_01  |    567 | //sum of 465   102
| value_02  |    294 | //sum of 186   108 
| value_03  |    570 | //sum of 245   570
|--------------------|

And most important, would this be scalable? i.e. to use it with three or more select "unions" or the performance would decrease a lot?

CodePudding user response：

The problem in your query is that you're aggregating on the single tables, then you are using UNION ALL to get your values. In order to solve your problem, you need to invert the order of these two operations:

first you apply the UNION function
then you aggregate with the SUM aggregation function

WITH CTE AS(
       SELECT COLUMN_01,
              COLUMN_02*.05 AS COLUMN
       FROM table
       WHERE COLUMN_03 = 1
         AND COLUMN_04 = 2
         AND COLUMN_05 = ALFA

       UNION ALL

       SELECT COLUMN_01,
              COLUMN_02*.45 AS COLUMN
       FROM table
       WHERE COLUMN_03 = 1
         AND COLUMN_04 = 2
         AND COLUMN_05 = BETA
) 
SELECT COLUMN_01,
       SUM(COLUMN) AS SUM_01
FROM CTE
GROUP BY COLUMN_01

CodePudding user response：

Use conditional aggregation:

SELECT COLUMN_01,
       SUM(COLUMN_02 * CASE COLUMN_05 WHEN 'ALFA' THEN 0.05 WHEN 'BETA' THEN 0.45 END) AS SUM_01
FROM tablename
WHERE COLUMN_03 = 1 AND COLUMN_04 = 2 AND (COLUMN_05 IN ('ALFA', 'BETA'))
GROUP BY COLUMN_01;