How to change the Group By column list when doing a Union

[deadsea]

Hi All:

Hope you are all doing well. I have stumbled across an issue in RS where were I am doing a UNION of two queries say ActualsQ and PlannedQ and SQL generated for this UNION is doing GROUP BY on new data items I added to make the two queries identical in terms of column name and type. Also, the new data items created in both queries before UNIONing them are set to cast(0, decimal).

ActualsQ has following columns:
A-Empl#
B-Empl Name
C-SalaryPaid
D-SalaryPaidToDate

PlannedQ has:

A-Empl#
B-Empl Name
C-PlannedSalary_Y1
D-PlannedSalary_Y2

When I perform a union I need to create 2 new columns (PlannedSalary_Y1 and PlannedSalary_Y2) in ActualsQ and 2 new columns in PlannedQ (SalaryPaid and SalaryPaidToDate) to ensure that we have same # of columns with same names. I created SalaryPaid and SalaryPaidToDate as cast(0, decimal) to make sure the data type implies that it is a fact in PlannedQ.

So far so good but when I run the report/query the UNION starts doing GROUP BY on 2 new columns (in addition to the two attribute columns) created for PlannedQ due to which summarization does not happen and I see records for Empl# and Empl Name repeated, where as they should be summarized.

Any ideas why this may be happening and how I can remove the unnecessary GROUP BY on new data items added to the query? Also, I am running C10.2.1 so I am also out of support I believe.

TIA and stay well!

[hespora]

The two new data items per query - have you supplied them with an aggregate function other than the default "Automatic"? Try setting them to "Total" and see what happens.

[adam_mc]

Also, do you really want to total Salary and Planned Salary into the same column after the union?

If not, I think you need to do it something like this:

ActualsQ

A- Empl#
B- Empl Name
C- SalaryPaid
D- SalaryPaidToDate
E- 0 as PlannedSalary_Y1
F- 0 as PlannedSalary_Y2

PlannedQ has:

A- Empl#
B- Empl Name
C- 0 as SalaryPaid
D- 0 as SalaryPaidToDate
E- PlannedSalary_Y1
F- PlannedSalary_Y2

Then set the aggregate function in the queries as follows:
A, B - None
C, D, E, F - Total

In this way, in the resulting union query, you should get the total C, D, E, F for every A, B.

Hope this helps,
Adam.