Compute Sum (Again)

I have tried everything to get a total of one of the columns in this query
including Rollup/Cube and compute sum. The problem is that compute sum canno
t
be used with select into and one cannot do a sum(count(distinct...
Rollup and cube give hierarchies and combination totals which is not what I
need.
I need something that operates as this should sum(count(distinct
a11.Policy_id)) but cannot find any function to do it. Can anyone please hel
p.
If one looks @. my select list, the final item is...
count(distinct(a11.Policy_id)) WJXBFS1
select a16.W_id W_id,
max(a16.w_desc) w_desc,
a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
a13.Pr_Group_id Pr_Group_id,
max(a13.Pr_group_desc) Pr_group_desc,
a12.Po_corp_unit_id Po_corp_unit_id,
count(distinct(a11.Policy_id)) WJXBFS1
into #ZZT4Z010YTNMD00B
from fat_bse_po_risk_detail a11
join POt_lu_policy a12
on (a11.Policy_id = a12.Policy_id)
join prt_lu_product a13
on (a11.product_id = a13.product_id)
join POv_lu_tr_Business_Type a14
on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
join TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
join TIt_lu_day a16
on (a11.Cur_trn_dt = a16.Cur_trn_dt)
where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Po_status_id in ('A')
and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a16.W_id in (select fiftytwo_w_roll_id from
tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
group by a16.W_id,
a14.Po_tr_bus_cat_id,
a13.Pr_Group_id,
a12.Po_corp_unit_idPlease explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. If you do that you should find
you get a helpful answer much faster. The following article should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. Please try to do that and you
should find you get a helpful answer much faster. The following article
should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||sum(count(distinct(a11.Policy_id))
this statement is odd and it is not allowed use a sub query in an agregade
function like sum
even if you could do so
if you have 1000 of count a11.Policy_id then you get the result as 1000.1000
(it will sum the count for every row of result set)
do you realy want that? if so you can create function then sum it
create function thefunction(@.w_id nvarchar(20))
returns int
as
begin
declare @.count_id as int
select @.count_id=count(Policy_id)
from fat_bse_po_risk_detail
Return IsNull(@.count,0)
end
then
select sum(thefunction(@.w_id))...
"marcmc" wrote:

> I have tried everything to get a total of one of the columns in this query
> including Rollup/Cube and compute sum. The problem is that compute sum can
not
> be used with select into and one cannot do a sum(count(distinct...
> Rollup and cube give hierarchies and combination totals which is not what
I
> need.
> I need something that operates as this should sum(count(distinct
> a11.Policy_id)) but cannot find any function to do it. Can anyone please h
elp.
> If one looks @. my select list, the final item is...
> count(distinct(a11.Policy_id)) WJXBFS1
> select a16.W_id W_id,
> max(a16.w_desc) w_desc,
> a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
> max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
> a13.Pr_Group_id Pr_Group_id,
> max(a13.Pr_group_desc) Pr_group_desc,
> a12.Po_corp_unit_id Po_corp_unit_id,
> count(distinct(a11.Policy_id)) WJXBFS1
> into #ZZT4Z010YTNMD00B
> from fat_bse_po_risk_detail a11
> join POt_lu_policy a12
> on (a11.Policy_id = a12.Policy_id)
> join prt_lu_product a13
> on (a11.product_id = a13.product_id)
> join POv_lu_tr_Business_Type a14
> on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
> join TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> join TIt_lu_day a16
> on (a11.Cur_trn_dt = a16.Cur_trn_dt)
> where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Po_status_id in ('A')
> and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
> 120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
> and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a16.W_id in (select fiftytwo_w_roll_id from
> tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
> group by a16.W_id,
> a14.Po_tr_bus_cat_id,
> a13.Pr_Group_id,
> a12.Po_corp_unit_id