November 11, 2015 at 7:37 am
Hello
I'm playing around with options regarding over and partition and was looking for a simple sum at a specific level to run alongside the detail
I can then look at percentages etc. e.g. T11.BROKER_COMM_AMNT / T11.ORIGINAL_PREMIUM
Using SQL 2012, I have the following code:
SELECT
T2.INSURER_POLICY_NO AS MAIN_COMP_BKEY
, T2.VERSION_REF VERSION_REF
, 0 AS COMMERCIAL_DISCOUNT
, sum(T11.ORIGINAL_PREMIUM) AS ORIGINAL_PREMIUM
, sum(T11.DISC_ORIG_PREM) AS DISC_ORIG_PREM
, sum(T11.BROKER_COMM_AMNT) AS BROKER_COMM_AMNT
, sum(T11.DISC_BROKER_COMM_AMNT) AS DISC_BROKER_COMM_AMNT
, sum(T11.BROKER_COMM_AMNT) over(partition by T2.INSURER_POLICY_NO) Totals
FROM
dbo.S_ACT_POLICY_PART T1
INNER JOIN dbo.S_ACT_POLICY T2 ON T1.POLICY_KEY = T2.POLICY_KEY
INNER JOIN DW_Enterprise_Test01.dbo.E_DWH_ACT_SECTION_PREMIUM_v2 T11
ON T1.POLICY_PART_KEY = T11.POLICY_PART_KEY
group by T2.INSURER_POLICY_NO, T2.VERSION_REF
the final sum is causing an issue:
Column 'DW_Enterprise_Test01.dbo.E_DWH_ACT_SECTION_PREMIUM_v2.BROKER_COMM_AMNT'
is invalid in the select list because it is not contained in either
an aggregate function or the GROUP BY clause.
I can't see why
If I remove the code, it works
T11.BROKER_COMM_AMNT works fine when summarised on its own
Can anybody see where I am going wrong?
Thanks
Damian.
- Damian
November 11, 2015 at 8:44 am
DamianC (11/11/2015)
Can anybody see where I am going wrong?
Yes, you can't mixed windowed and windowless aggregates in the same SELECT clause. If I understand what you want, you'll want to use a CTE to compute the detailed sums and then in the outer query use a windowed aggregate to calculate the grand total and get the percentages.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2015 at 8:58 am
Ah, ok I'll go the CTE route
Did consider that but thought I'd try partition and over
Thanks
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply