COMPUTE SUM

How Can I get a computed sum on the last column in the select list
distinct(a11.Policy_id).
We can't use sum(count(... or compute sum in a select into statement so I am
really stumped...
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_idUse CUBE or ROLLUP.
http://msdn.microsoft.com/library/e..._qd_08_6g9x.asp
COMPUTE / COMPUTE BY exists for backwards compatibility reasons so you
should avoid it in any new code.
David Portas
SQL Server MVP
--|||Hi
I have simplified the query but I think you've got an idea
use northwind
select sum(o) orderid into #test
from (select count(*) o from orders) as d
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:23A4514D-EFA0-4965-87AD-0D3A7121B935@.microsoft.com...
> How Can I get a computed sum on the last column in the select list
> distinct(a11.Policy_id).
> We can't use sum(count(... or compute sum in a select into statement so I
am
> really stumped...
> 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|||David: Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
Uri, I cannot see what you are trying
This really is a clincher. There must be some way of totalling a distinct|||You haven't specified exactly what you want to sum. Sub Totals or just a
Grand Total for the result?
Here are two examples taken from the NorthWind database. Notice that the
answer is different in each case, depending on how you want to interpret a
"DISTINCT" total (number of distinct values or total of the distinct counts)
.
SELECT shipcountry, shipcity, COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity
UNION ALL
SELECT NULL, NULL, COUNT(DISTINCT productid)
FROM Invoices
SELECT shipcountry, shipcity, COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity
UNION ALL
SELECT NULL, NULL, SUM(dist)
FROM
(SELECT COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity) AS T(dist)
If you want sub-totals as well then you just need to add a GROUP BY list of
the second UNIONed query.
David Portas
SQL Server MVP
--|||thx for replying David.
I want a total sum like the compute sum operates on my last item in the
select list which is
count(distinct a11.Policy_id) WJXBFS1|||As it's just a straight total you can use ROLLUP:
SELECT
w_id, w_desc, po_tr_bus_cat_id, po_tr_bus_cat_desc,
pr_group_id, pr_group_desc, po_corp_unit_id, SUM(wjxbfs1) AS wjxbfs1
INTO #ZZT4Z010YTNMD00B
FROM
(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
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 >= '20040201'
AND a11.Inception_date_id < '20050130'
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) AS T
GROUP BY w_id, w_desc, po_tr_bus_cat_id, po_tr_bus_cat_desc,
pr_group_id, pr_group_desc, po_corp_unit_id
WITH ROLLUP
HAVING GROUPING(w_id)=1
OR GROUPING(po_corp_unit_id)=0
(this is untested)
P.S. You don't need to CONVERT date strings if you stick to the ISO date
format above.
David Portas
SQL Server MVP
--|||thanks again David, I am running it now... what does the...
HAVING GROUPING(w_id)=1
OR GROUPING(po_corp_unit_id)=0
do?|||If you run the query without the HAVING part you'll see that the result set
has subtotals for each column in the GROUP BY list. The GROUPING() criteria
ensures that we only include the total row you wanted (the grand total) plus
the original rows. See Books Online for details of the GROUPING syntax.
David Portas
SQL Server MVP
--sqlsql