Aggr

Hi,

What I want is to to get SUM of col1 and list quarter data when
applicable.

DDL:
create table #temp (col1 int, rent int, transport int, qtr smallint,
other int);

DML:
insert into #temp
values(1, 800, 300, 1, 200)

insert into #temp
values(1, 800, 300, 2, 300)

insert into #temp
values(2, 800, 300, 2, 400)

Data retrieval DML:
select col1, sum(other) as other_Total, case when qtr = 1 then
sum(other) end qtr_total
from #temp
group by col1,qtr

Current Resultset:
col1 other_Total qtr_total
---- ---- ----
1 200 200
1 300 NULL
2 400 NULL

Desirable Resultset: (get ride of the middle row above and add up the
200 and 300), so, it would look like
col1 other_Total qtr_total
---- ---- ----
1 500 200
2 400 NULL

What am I missing here?

TIA.SELECT col1, SUM(other) AS other_total,
SUM(CASE WHEN qtr = 1 THEN other END) qtr_total
FROM #temp
GROUP BY col1

--
David Portas
SQL Server MVP
--|||Thank you, David.

Don