How to GROUP BY on a UNION query to summarise the UNION ed data set

Shanika Dias shared this question 20 months ago
Answered

Hi,

I am calculating the Savings captured in two levels - booking level OR item level.

My data set is as follows :


Level 1 : Saving is at Booking level. That is one saving amount for the booking as a whole is captured. Joining with Item table resulted saving amount to repeat. But the final report must not consider repeated value when summing up.


Booking   Item     Type    Cost    Saving

1         01       A	   1000    100
1         02       A	   1000    100
1         03       A	   1000    100
2         01       B        250     10
2         02       B        250     10
3         01       A        500     50
Level 2 : Saving is at item level. That is a saving per item is captured. Below is Booking table joined with Item table.


Booking  Item    Type   Cost   Saving

4	 01	 A	 2000     500
4	 02	 A	 1500     200
4	 03	 B        250      10
5	 05	 B        750      50
In my report, I have an intersect query and a union query to achieve, above level 1 data set and level 2 data set.


Finally as the report output, what I need to achieve is as follows :


Type  Cost    Saving
A     5000    850
B     1250     70
What I end up is as below, which is a break down from each level per Type:


Type  Cost    Saving
A     1500    150
A     3500    700
B      250     10
B     1000     60
This is because I am not sure how to apply an outer level group by to the query so that each Type results in one row with summed up values. Please let me know how to achieve this?


Cheers,

Shanika

Comments (9)

photo
1

Hi Shanika,

Thanks for reaching out with your question. Report creation as it pertains to specific client data falls outside of the Yellowfin Support scope and more under the Consulting category. I'm happy to provide suggestions as to how this should be working.

To prevent having multiple rows for each 'Type', you will want to apply an aggregation to both of your metric columns. It sounds like in this case you will want a 'Sum' aggregation.

I don't have a 7.1 instance running to test with, since it has been End of Life for Support for some time now. In supported versions you can apply a metric by clicking the arrow next to the metric on the 'Data' tab of the Report Editor and choosing your 'Aggregation':


96bac253006b4dd84a8c465c8ab9fe72

Let me know if this helps.

Thanks,

Ryan

photo
1

Hi Ryan,

Thank you for your reply. In my scenario, in the main query, I don't want the SUM to be applied for Saving column as it's saved only at booking level - 1 saving value per booking. If I apply SUM on the Saving, the values multiply by the number of items, which is inaccurate. I have applied SUM on Cost column already.


Further more in my UNION query where I do the Item level savings query, I have applied the SUM on both the metric columns as in this case I need the item level Savings value totals as opposed to the Booking level.

Shanika

photo
1

Hi Shanika,

Thanks for the reply. Another option in this case may be to use the 'Group Data' option to combine your rows. In my example, I'm combining 'Adventure' and 'Culture' into A.

/ByyXACadZ+pwAAAAAElFTkSuQmCCAA==

It's a bit difficult to comment on how to best achieve this, as I don't have any picture of your Report Builder to see how it's all configured. If the Group Data option doesn't work for you, kindly provide some additional screenshots of your Report Builder so that I can better visualize what's happening on the Yellowfin side of things.

Thanks,

Ryan

photo
1

Your outer query is joined on "item". Because it is a sub-query and not a true table join, "item" is going to be included in the group by of the sub-query. I've asked for a way to be able to sum after the join as an idea but that may . You're going to have to revamp your view so that your table is joined in the view rather than the report, again because of the sub-query. Then, you can union making booking and item null on the report. What I typically do is create a calculated field with 'Total' as the value for booking.

photo
1

Hi Larry,

Thanks for providing your Answer. Let us know how it all goes Shanika.

Thanks,

Ryan

photo
1

Thanks Ryan and Larry,

Larry : My Item table is joined at the view level. So its an Item based.

In the Booking level (Intersect Query):

I apply SUM on Cost as I need the individual Item costs summed up to get total cost for booking.

I don't apply SUM on Saving as I need to get the Saving at the Booking level which is one Saving value per booking.


In the Item Level (Union Query) :

I apply SUM on each Item Cost as I need the individual Item costs summed up to get total cost for booking.

I apply SUM on Saving as I need to get the Saving at the Item level calculated.

Now I need these two results to be merged together so that I can have summary level detail for each Type having total cost and Saving calculated at individual subqueries.


Ryan : I am not sure how to apply Group in my case.


Thanks,

Shanika

photo
1

Hi Shanika,

Thanks for the reply. Report creation specific to data and joins starts falling outside of the scope of Yellowfin Support and is generally handled by Consulting directly. I'm happy to reach out to your Account Manager regarding this if it's a path you wish to pursue.

I look forward to your reply.

Thanks,

Ryan

photo
1

Hi Shanika,

I wanted to check in on this and see if you wanted assistance in contacting your Account Manager regarding possible consulting for this item. If I don't hear back, I'll presume we're ok to close this out.

Thanks,

Ryan

photo
1

Hi Shanika,

I'm going to go ahead and mark this as Answered, as I haven't heard back for some time.

Thanks,

Ryan