Sum question

[mirth]

Hi all,

Is there any obvious way to sum only certain rows. I suppose it's
similar to cube or rollup but I can't get either of them to work. what
I basically have is this...

CREATE TABLE #TempTable (
[GLD_ACCTNG_PER] INT,
[OrderNum] INT,
[SummaryDescript ion] varchar(50),
[SummaryAmount] decimal
)

INSERT INTO #TempTable
SELECT GLD_ACCTNG_PER,
'OrderNum' = CASE
WHEN (ACT_GL_NO between '4000' and '4399') THEN 1
WHEN ((ACT_GL_NO between '4400' and '4499') and (ACT_GL_NO <>
'4440')) THEN 2
WHEN (ACT_GL_NO = '4440') THEN 3
WHEN (ACT_GL_NO between '5000' and '5399') THEN 4
WHEN ((ACT_GL_NO between '5400' and '5499') and (ACT_GL_NO <>
'5440')) THEN 5
WHEN (ACT_GL_NO = '5440') THEN 6
WHEN ((ACT_GL_NO between '5850' and '5999')) THEN 7
WHEN ((ACT_GL_NO between '6000' and '6009')) THEN 8
ELSE 10000
END,
'SummaryDescrip tion' = CASE
WHEN (ACT_GL_NO between '4000' and '4399') THEN 'TotExtSales'
WHEN ((ACT_GL_NO between '4400' and '4499') and (ACT_GL_NO <>
'4440')) THEN 'TotIntSales'
WHEN (ACT_GL_NO = '4440') THEN 'InterMarkUp'
WHEN (ACT_GL_NO between '5000' and '5399') THEN 'TotExtCosts'
WHEN ((ACT_GL_NO between '5400' and '5499') and (ACT_GL_NO <>
'5440')) THEN 'TotIntCosts'
WHEN (ACT_GL_NO = '5440') THEN 'MarkUpCharges'
WHEN ((ACT_GL_NO between '5850' and '5999')) THEN 'Adjustments'
WHEN ((ACT_GL_NO between '6000' and '6009')) THEN 'Labour Costs'
ELSE 'Empty'
END,
'SummaryAmount' = SUM(CASE
WHEN (ACT_GL_NO between '4000' and '4399') THEN GLD_Total
WHEN ((ACT_GL_NO between '4400' and '4499') and (ACT_GL_NO <>
'4440')) THEN GLD_Total
WHEN (ACT_GL_NO = '4440') THEN GLD_Total
WHEN (ACT_GL_NO between '5000' and '5399') THEN GLD_Total
WHEN ((ACT_GL_NO between '5400' and '5499') and (ACT_GL_NO <>
'5440')) THEN GLD_Total
WHEN (ACT_GL_NO = '5440') THEN GLD_Total
WHEN ((ACT_GL_NO between '5850' and '5999')) THEN GLD_Total
WHEN ((ACT_GL_NO between '6000' and '6009')) THEN GLD_Total
ELSE 0
END)

FROM test.dbo.Financ ePeriod
WHERE ....9varios where clauses....
GROUP BY ACT_GL_NO, GLD_ACCTNG_PER

Now the data from this ~temp table is selected like this.....
SELECT [Summary Description],
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'01') THEN
[Summary Amount] ELSE 0 END) AS '200401',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'02') THEN
[Summary Amount] ELSE 0 END) AS '200402',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'03') THEN
[Summary Amount] ELSE 0 END) AS '200403',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'04') THEN
[Summary Amount] ELSE 0 END) AS '200404',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'05') THEN
[Summary Amount] ELSE 0 END) AS '200405',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'06') THEN
[Summary Amount] ELSE 0 END) AS '200406',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'07') THEN
[Summary Amount] ELSE 0 END) AS '200407',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'08') THEN
[Summary Amount] ELSE 0 END) AS '200408',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'09') THEN
[Summary Amount] ELSE 0 END) AS '200409',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'10') THEN
[Summary Amount] ELSE 0 END) AS '200410',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'11') THEN
[Summary Amount] ELSE 0 END) AS '200411',
SUM(CASE (GLD_ACCTNG_PER ) WHEN convert(int, @Period+'12') THEN
[Summary Amount] ELSE 0 END) AS '200412'
FROM #TempTable
WHERE .....various where clauses......
GROUP BY [Order Num], [Summary Description]
ORDER BY [Order Num]

Now this is obviously crosstabbed to give months as rows. the problem
is that I need to sum together various rows either on the insert or
the select and still be able to crosstab them by month i.e on the
insert rows with OrderNum need to be summed together and stored as a
different summarydescript ion. this has to be done for various
combinations of ordernums (not all of them are in this post).

can anyone suggest a tidy way of doing this? Assuming you understand
what I'm waffling on about of course :)

Thanks.

[Erland Sommarskog]

Re: Sum question

mirth (contactmirth@y ahoo.co.uk) writes:[color=blue]
> Now this is obviously crosstabbed to give months as rows. the problem
> is that I need to sum together various rows either on the insert or
> the select and still be able to crosstab them by month i.e on the
> insert rows with OrderNum need to be summed together and stored as a
> different summarydescript ion. this has to be done for various
> combinations of ordernums (not all of them are in this post).
>
> can anyone suggest a tidy way of doing this? Assuming you understand
> what I'm waffling on about of course :)[/color]

Yeah, the latter part is a bit of a problem. There is a standard
recommendation, and that is that you post:

o CREATE TABLE statements for your table(s) in question.
o INSERT statements with sample data.
o The desired output given the sample data.

That makes a little easier to understand what you are asking about, and
also makes it possible to post a tested solution.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mirth]

Re: Sum question

Erland, I thought I did provide a table creation, I also provided an
example of how the data fills the table. As far as output
basically it will look like this currently.

Description 200301 200302 200303 200304 200305 200306 200307
200308 200309 200311 200312
_______________ _______________ _______________ _______________ _______________ _______________ ___________
TotExtSales 100 200 300 200 300 400 100 300 300 0 0
TotIntSales 100 200 300 200 300 400 100 300 300 0 0
TotExtCosts 700 650 400 150 220 330 450
175 350 0 0


I need the output to look like this.....

Description 200301 200302 200303 200304 200305 200306 200307
200308 200309 200311 200312
_______________ _______________ _______________ _______________ _______________ _______________ ___________
TotExtSales 100 200 300 200 300 400 100 300 300 0 0
TotIntSales 100 200 300 200 300 400 100 300 300 0 0

Total Sales 200 400 600 400 600 800 200
600 600 0 0

TotExtCosts 700 650 400 150 220 330 450
175 350 0 0

Basically I need to sum the columns TotExtSales and TotIntSales for
each monthly period. This hads to happen
for various combinations of rows through out the resultset.

Does this make it any clearer?


Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns94FCEE EF642Yazorman@1 27.0.0.1>...[color=blue]
> mirth (contactmirth@y ahoo.co.uk) writes:[color=green]
> > Now this is obviously crosstabbed to give months as rows. the problem
> > is that I need to sum together various rows either on the insert or
> > the select and still be able to crosstab them by month i.e on the
> > insert rows with OrderNum need to be summed together and stored as a
> > different summarydescript ion. this has to be done for various
> > combinations of ordernums (not all of them are in this post).
> >
> > can anyone suggest a tidy way of doing this? Assuming you understand
> > what I'm waffling on about of course :)[/color]
>
> Yeah, the latter part is a bit of a problem. There is a standard
> recommendation, and that is that you post:
>
> o CREATE TABLE statements for your table(s) in question.
> o INSERT statements with sample data.
> o The desired output given the sample data.
>
> That makes a little easier to understand what you are asking about, and
> also makes it possible to post a tested solution.[/color]

[Hugo Kornelis]

Re: Sum question

On 3 Jun 2004 00:45:42 -0700, mirth wrote:
[color=blue]
>Erland, I thought I did provide a table creation, I also provided an
>example of how the data fills the table. As far as output
> basically it will look like this currently.
>
>Description 200301 200302 200303 200304 200305 200306 200307
>200308 200309 200311 200312
>______________ _______________ _______________ _______________ _______________ _______________ ____________
>TotExtSales 100 200 300 200 300 400 100 300 300 0 0
>TotIntSales 100 200 300 200 300 400 100 300 300 0 0
>TotExtCosts 700 650 400 150 220 330 450
> 175 350 0 0
>
>
>I need the output to look like this.....
>
>Description 200301 200302 200303 200304 200305 200306 200307
>200308 200309 200311 200312
>______________ _______________ _______________ _______________ _______________ _______________ ____________
>TotExtSales 100 200 300 200 300 400 100 300 300 0 0
>TotIntSales 100 200 300 200 300 400 100 300 300 0 0
>
>Total Sales 200 400 600 400 600 800 200
> 600 600 0 0
>
>TotExtCosts 700 650 400 150 220 330 450
> 175 350 0 0
>
>Basically I need to sum the columns TotExtSales and TotIntSales for
>each monthly period. This hads to happen
> for various combinations of rows through out the resultset.
>
>Does this make it any clearer?[/color]

Hi Mirth,

Yes, it does. I think the easiest way to add the extra subtotals line is
to just insert the summary data in the temp table before doing the final
crosstab select. Like this:

INSERT INTO #TempTable (
GLD_ACCTNG_PER,
OrderNum,
SummaryDescript ion,
SummaryAmount)
SELECT
GLD_ACCTNG_PER,
OrderNum,
'Total Sales',
SUM(SummaryAmou nt)
FROM #TempTable
WHERE SummaryDescript ion IN ('TotExtSales', 'TotIntSales')
GROUP BY GLD_ACCTNG_PER, OrderNum

(untested)

Note - you'll probably have to use CASE in the ORDER BY clause to get the
listing in the desired order.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[mirth]

Re: Sum question

Thanks, I actually sussed it.

I ended up building a rather complicated cursor to do all. I couldn't
just sum the column Hugo as it required data from another table.
Anyway it works. Thanks again.

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<34ptb0lcf lapn3ou2ebb9bo7 lulvhb5g64@4ax. com>...[color=blue]
> On 3 Jun 2004 00:45:42 -0700, mirth wrote:
>[color=green]
> >Erland, I thought I did provide a table creation, I also provided an
> >example of how the data fills the table. As far as output
> > basically it will look like this currently.
> >
> >Description 200301 200302 200303 200304 200305 200306 200307
> >200308 200309 200311 200312
> >______________ _______________ _______________ _______________ _______________ _______________ ____________
> >TotExtSales 100 200 300 200 300 400 100 300 300 0 0
> >TotIntSales 100 200 300 200 300 400 100 300 300 0 0
> >TotExtCosts 700 650 400 150 220 330 450
> > 175 350 0 0
> >
> >
> >I need the output to look like this.....
> >
> >Description 200301 200302 200303 200304 200305 200306 200307
> >200308 200309 200311 200312
> >______________ _______________ _______________ _______________ _______________ _______________ ____________
> >TotExtSales 100 200 300 200 300 400 100 300 300 0 0
> >TotIntSales 100 200 300 200 300 400 100 300 300 0 0
> >
> >Total Sales 200 400 600 400 600 800 200
> > 600 600 0 0
> >
> >TotExtCosts 700 650 400 150 220 330 450
> > 175 350 0 0
> >
> >Basically I need to sum the columns TotExtSales and TotIntSales for
> >each monthly period. This hads to happen
> > for various combinations of rows through out the resultset.
> >
> >Does this make it any clearer?[/color]
>
> Hi Mirth,
>
> Yes, it does. I think the easiest way to add the extra subtotals line is
> to just insert the summary data in the temp table before doing the final
> crosstab select. Like this:
>
> INSERT INTO #TempTable (
> GLD_ACCTNG_PER,
> OrderNum,
> SummaryDescript ion,
> SummaryAmount)
> SELECT
> GLD_ACCTNG_PER,
> OrderNum,
> 'Total Sales',
> SUM(SummaryAmou nt)
> FROM #TempTable
> WHERE SummaryDescript ion IN ('TotExtSales', 'TotIntSales')
> GROUP BY GLD_ACCTNG_PER, OrderNum
>
> (untested)
>
> Note - you'll probably have to use CASE in the ORDER BY clause to get the
> listing in the desired order.
>
> Best, Hugo[/color]

[Hugo Kornelis]

Re: Sum question

On 4 Jun 2004 00:55:39 -0700, mirth wrote:
[color=blue]
>Thanks, I actually sussed it.
>
>I ended up building a rather complicated cursor to do all. I couldn't
>just sum the column Hugo as it required data from another table.
>Anyway it works. Thanks again.[/color]

Hi Mirth,

A cursor? Aaaarrrggghhh!! !!

Are you sure you couldn't get the other table's data with a join? (just
asking)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Sum question

[posted and mailed, please reply in news]

mirth (contactmirth@y ahoo.co.uk) writes:[color=blue]
> Erland, I thought I did provide a table creation, I also provided an
> example of how the data fills the table.[/color]

Yes, but that does not help me. I don't have your tables to insert
from. To post a tested solution, I need the data too. And, since I'm
lazy, and you are the one who is having the problem, I task you to
provide it for me in a form that is simple to use for me. :-)

Anyway, I see that you have gone for a cursor. Here is however a demon-
stration a technique you could have used:

SELECT coalesce(Custom erID, 'Total cat ' + Custcat), cnt
FROM (SELECT Custcat, CustomerID, cnt = COUNT(*)
FROM (SELECT Custcat = CASE WHEN CustomerID IN ('ALFKI', 'BERGS')
THEN 'A'
ELSE 'B'
END,
CustomerID
FROM Northwind..Orde rs
WHERE CustomerID IN ('ALFKI', 'BERGS', 'VICTE')) AS x
GROUP BY Custcat, CustomerID
WITH ROLLUP
HAVING Custcat = 'A' OR CustomerID IS NOT NULL) AS y
ORDER BY Custcat

I could not use your example directly, as for reasons quoted above, I could
not test it.

The basic idea is that ROLLUP give you the intermediate sums. It appeared
that in your example, you only wanted it for a special case, and this
is reflected in the HAVING clause. HAVING operates on the result produced
by GROUP BY.

Then I use a couple of derived tables so that I don't have to repeat
expressions.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp