My query is- http://sqlfiddle.com/#!17/9e89e/761
On average profit, the formula needs to group by aggregate function.So not getting required profit My query is attached above link and the required output is attached by image. please help me.
-
Please do not add random irrelevant product tags to your question!Shadow– Shadow2021-08-12 06:56:06 +00:00Commented Aug 12, 2021 at 6:56
-
I thought it will reach multiple developers. Next time I will take careanupam tiwari– anupam tiwari2021-08-12 07:00:04 +00:00Commented Aug 12, 2021 at 7:00
-
1Please do not upload images of code/errors when asking a question.Thorsten Kettner– Thorsten Kettner2021-08-12 07:04:02 +00:00Commented Aug 12, 2021 at 7:04
-
2@anupamtiwari yes, your question reached more developers and then annoyed them as it had nothing to do with the product they know...Shadow– Shadow2021-08-12 07:28:11 +00:00Commented Aug 12, 2021 at 7:28
Add a comment
|
1 Answer
You can get the count of the different universe first using count(*) over (partition by u.universe) and after that you can apply your logic to calculate profit like the following query.
select c.id,c.name,u.universe,u.budget,u.revenue
,(revenue - budget) / count(*) over (partition by u.universe) as profit
from universe u
inner join characters c on u.id = c.universe_id
order by u.universe desc
Check the updated fiddle here
2 Comments
Thorsten Kettner
You don't need a subquery for this. You can divide by
count(*) over (...) directly.
PSK
@ThorstenKettner, you are right, thanks for the suggestion, I have updated my answer.