How to Obtain Subtotal Like Output?

Question

Post reply

How to Obtain Subtotal Like Output?

Neetu Sharma

SSC Enthusiast

Points: 153
More actions
March 29, 2006 at 12:22 am

#112567

Hi ALL,
My Query is to obtain Subtotal like output. I am using below mentioned Query
select year(DateEntered)as yr ,branch,count(*) as TRec from stat group by year(DateEntered),Branch
PRESENT OUTPUT:
Yr         Branch          TRec
2004      1                 1000
2004      2                  500
2005      1                  3000
2005      2                   2000
DESIRED OUTPUT:
Yr         Branch          TRec      YearlyTotal
2004      1                 1000
2004      2                  500
                                              1500
2005      1                  3000
2005      2                   2000
                                              5000
Pls. Help me in this matter. Thanks in advance.
Kindest Regards,
Neetu Sharma

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Hi Neetu,

You can use 'with rollup'...

--data

declare @t table (Yr int, Branch int, TRec int)

insert @t

select 2004, 1, 1000

union all select 2004, 2, 500

union all select 2005, 1, 3000

union all select 2005, 2, 2000

--Basic statement

select Yr, Branch, sum(TRec) as TRec from @t group by Yr, Branch with rollup

--Your requirement

select

case when branch is null then null else Yr end,

Branch,

case when branch is null then null else sum(TRec) end as TRec,

case when branch is null then sum(TRec) else null end as YearlyTotal

from @t

group by Yr, Branch

with rollup

having Yr is not null

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Vikas-299648 SSC Rookie Points: 32 More actions · Answer 2

Hi Neetu

Use this query to get required output,

select year(DateEntered)as yr ,branch,count(*) as TRec ,NULL as YearlyTotal

from stat

group by year(DateEntered),Branch

union

Select NULL,NULL,NULL, count(*) as YearlyTotal

from stat

group by

year(DateEntered)

Hope this will work

Vikas

Neetu Sharma SSC Enthusiast Points: 153 More actions · Answer 3

Thanks all, it has helped me to certain extent.

Kindest Regards,

Neetu Sharma

kevin mann Hall of Fame Points: 3894 More actions · Answer 4

--I don't know if this fits what you are looking for but I will post anyhow, because I never see anyone use compute

declare @t table (Yr int, Branch int, TRec int)

insert @t

select 2004, 1, 1000

union all select 2004, 2, 500

union all select 2005, 1, 3000

union all select 2005, 2, 2000

SELECT Yr, Branch, TRec

FROM @t

ORDER BY Yr,Branch

COMPUTE SUM(TRec) BY Yr

COMPUTE SUM(TRec)

kevin mann Hall of Fame Points: 3894 More actions · Answer 5

even better.... I forgot about ROLLUP

--data

declare @t table (Yr int, Branch int, TRec int)

insert @t

select 2004, 1, 1000

union all select 2004, 2, 500

union all select 2005, 1, 3000

union all select 2005, 2, 2000

SELECT yr, branch, SUM(TRec)

FROM @t

GROUP BY yr, branch WITH ROLLUP

HAVING GROUPING(yr) = 0

noeld SSC Guru Points: 96590 More actions · Answer 6

This is a presentation issue and the best way to hadle it is at client side. If this calculation is to move data into anther table then you do need the above queries but if this is to be presented by any reporting tool it will be better to handle it there

Cheers,

* Noel

kevin mann Hall of Fame Points: 3894 More actions · Answer 7

kevin mann

Hall of Fame

Points: 3894

March 29, 2006 at 12:02 pm

#629618

Noel,

Who asked you? KIDDING!

CHEERS!