I have the list of emitted invoices divided by year and by group (activity sector) of the company ( year=y group=g ).
I need to get a) the total by year and group b) the total by year c) the percentage of each group over the total of each year.
I would like to do something like:
select y,g,sum(net0) totalOverYearAndGroup,sum(net0) over(partition by y) TotalOverYear from facdet where inbound=0 group by y,g;
But it does not work as expected and I don't even understand the required semantics at the end of all. Is there a way of doing it the "short way" and according to specs ?
Until now the only hack solution ( that I don't like as it is not clear to me neither safe or elegant neither I know why it works) is:
select **distinct** y,g,sum(net0) over(partition by y,g) ygsum,sum(net0) over(partition by y) ysum from facdet where inbound=0
and wanting to avoid writing to much to get the percentage too
select *,round(100*ygsum/ysum) perc from
(select distinct y,g,sum(net0) over(partition by y,g) ygsum,sum(net0) over(partition by y) ysum from facdet where inbound=0) a;