February 9, 2006 at 7:26 am
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select Distinct CustomerName,
CustomerNumber,
FGoal AS FG,
FSched,
(cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='09'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
February 9, 2006 at 7:30 am
Take out the columns in the select. You need them in the group by but you are not required to return them. Also your distinct is redundant with the group by.
February 9, 2006 at 8:08 am
I tried:
Select (cast(FGoal as numeric(30,2)) / FeeSched) * 100 AS gt
from DR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FGoal,
FeeSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FeeSched) * 100)
this returns the sums in the select, not the grand total from the COMPUTE
February 9, 2006 at 8:46 am
The COMPUTE clause is retuned as it's own separate resultset.
In you receiving end, it's the 2nd result returned, the first being the SELECT list.
You could look at ROLLUP, which can produce the same grand total for you. It returns all in a single resultset, so it's much easier to filter on.
select x.a,
sum(x.b)
from (
select 1 as a, 10 as b union all
select 1 as a, 100 as b union all
select 1 as a, 20 as b union all
select 2 as a, 35 as b
) x
group by x.a
with rollup
a
----------- -----------
1 130
2 35
NULL 165
To filter out everything but the bottom grand total:
select x.a,
sum(x.b)
from (
select 1 as a, 10 as b union all
select 1 as a, 100 as b union all
select 1 as a, 20 as b union all
select 2 as a, 35 as b
) x
group by x.a
with rollup
having grouping(x.a) = 1
a
----------- -----------
NULL 165
(1 row(s) affected)
/Kenneth
February 9, 2006 at 8:54 am
Ken could you help me form mine with your suggestion?
February 9, 2006 at 9:12 am
Try and see if this works.
(You just wanted the final grand total row, right?)
Select CustomerName,
CustomerNumber,
FGoal AS FG,
FSched,
(cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR
WHERE e='09'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
with rollup
having grouping(CustomerName) = 1
I can't run it, but the idea is that your row should like like
CustomerName CustomerNumber FG FSched gt
------------ -------------- ------ ------ -------
NULL NULL NULL NULL 1234
/Kenneth
February 9, 2006 at 9:16 am
oh, no, what i need is one sum returned..the value by the rollup...there should only be one value, not a whole line
February 10, 2006 at 2:15 am
Like this then?
select x.gt
from (
Select CustomerName,
CustomerNumber,
FGoal AS FG,
FSched,
(cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR
WHERE e='09'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
with rollup
having grouping(CustomerName) = 1
) x
/Kenneth
February 10, 2006 at 3:18 am
If you are going to use a subquery the "with rollup" is unnecessary. But you do need another aggregate 😉
select sum(x.gt)
from (
Select
(cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR
WHERE e='09'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
having grouping(CustomerName) = 1
) x
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply