Getting sum of a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting sum of a column

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-25 : 11:45:47

This procedure gives me the below table results. How do I sum up all Area 1 and only have one displayed with the total?

@Area varchar(2),
@Dist varchar(3)

AS

Select
Case
when sort = 'x' then 'Area ' + right(reg,1)
--when sort = 'x' then 'Area ' + right(reg,2)
else reg
end AS reg1,

reg,
region,
MMPend,
RecPend,
CAST(MMCleared * 1.0 / MMPend * 100 AS decimal(7, 2)) AS Pend%,
MMCleared,
RecCleared,
CAST(RecCleared * 1.0 / RecPend * 100 AS decimal(7, 2)) AS Clr%
FROM

(SELECT Sort,reg,
region,
SUM(MMPending) AS MMPend,
SUM(RecPending) AS RecPend,
SUM(MMClr) AS MMClr,
SUM(RecClr) AS RecClr
from MMTotals

where (sort = '1') or (area = @area and sort = 'x')
or (dist = @dist and sort = 'y')or (dist = @dist and sort = 'z')

group by sort, Reg, Region
)a
order by sort


Reg1    reg   region   MMPend   RecPend  Pend%  MMclr  RecClr  Clr%
NAT	NAT	0	247283	152577	1.41	3488	1978	1.30
Area 1	001	A	2517	1684	1.63	41	28	1.66
Area 1	001	B	4415	2716	2.06	91	39	1.44
Area 1	001	C	8106	4701	1.74	141	61	1.30
Area 1	001	D	5250	3355	1.77	93	53	1.58
Area 1	001	E	4572	2767	1.44	66	23	0.83
Area 1	001	G	3039	1927	1.09	33	27	1.40
Area 1	001	H	7662	4570	1.38	106	64	1.40
Area 1	001	I	2441	1622	2.05	50	25	1.54
Area 1	001	J	1591	960	0.82	13	9	0.94
Area 1	001	K	2894	1956	1.49	43	27	1.38
200	200	C	613	303	1.31	8	3	0.99
009	009	C	228	128	2.19	5	1	0.78
200	200	C	385	175	0.78	3	2	1.14

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-25 : 12:22:53

Never mind I had to add the Region parameter and add it to my where clause.

@Region varchar(1),
@Area varchar(2),
@Dist varchar(3)

AS

Select
Case
when sort = 'x' then 'Area ' + right(reg,1)
else reg
end AS reg1,

sort,
reg,
region,
MMPend,
RecPend,
CAST(MMCleared * 1.0 / MMPend * 100 AS decimal(7, 2)) AS PendingPercent,
MMCleared,
RecCleared,
CAST(RecCleared * 1.0 / RecPend * 100 AS decimal(7, 2)) AS ClearedPercent
FROM

(SELECT Sort,reg,
region,
SUM(MMPending) AS MMPend,
SUM(RecPending) AS RecPend,
SUM(MMCleared) AS MMCleared,
SUM(RecCleared) AS RecCleared
from MMTotals

where (sort='1') or (sort = @Region) or (region= @Region and area = @Area and sort = 'x') or
(region= @Region and area = @Area and sort = 'y' and dist=@Dist)
or (region= @Region and area = @Area and sort = 'z' and dist=@Dist )
--order by sort, reg

--where (sort = '1') or (area = @area and sort = 'x')
-- or (dist = @dist and sort = 'y')or (dist = @dist and sort = 'z')

group by sort, Reg, Region
)a
order by sort



NAT	1	NAT	0	247283	152577	1.41	3488	1978	1.30
PHI	C	PHI	C	32808	19969	1.62	533	288	1.44
Area 1	x	001	C	8106	4701	1.74	141	61	1.30
200	y	200	C	613	303	1.31	8	3	0.99
009	z	009	C	228	128	2.19	5	1	0.78
200	z	200	C	385	175	0.78	3	2	1.14

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources