Hi there,
I'm stuck for quite a while on this problem and hope you can kick me in the right direction. I'm building a billing system for an audiobook distributor. They publish audiobooks for their publishers on two platforms (Bookwire=bw and Audible=ad). They get the sales data every quarter and must calculate the total share per publisher and platform.
I have a table with all the sales data. I also have a table with all information regarding every single book, where we find the agreed share allocation key (e.G. 70% for the author).
In order to calculate the payout for the publisher, I query all unbilled sales data, group them by
- publisher
- platform
- year
- quarter
- book
and aggregate the sum of the royalties per platform:
Then I loop over the output and calculate the payment (aka multiply the royalties with the allocation key of the book).
Now I have the payment for every publisher per quarter, platform, and book. But I need to cumulate it one step further and sum up the royalties so that they're not per book anymore. And here I struggle.
In the end, I need a result like this:
Year___Quarter_Invoice Text__________Amount
2023___Q1______Payout Bookwire_____123.48
2023___Q1______Payout Audible_______123.48
How can I summarize the 'bw_total_royalties' and the 'ad_total_royalties'?
I tried to find all unique publisher Id's and loop over those to find all connected items in the array from the query and go from there, but it didn't work.
I guess the solution is not so hard to create, but I need a fresh view on the problem :-)
Any help is appreciated.
Thanks in advance,
Stefan