September 28, 2009 at 10:21 am
I have a numeric data field and I am trying to run a summarization with rollup. The code pulls the data I need, but it is ADDING all numbers. Problem is the column contains negative numbers that should be subtracted from the total and not added! Any pointers here?
September 28, 2009 at 11:45 am
I just tried this and got the right results:
create table #T (
ID int identity primary key,
Dim int,
Val int);
insert into #T (Dim, Val)
select 1, 1 union all
select 1, 2 union all
select 2, 1 union all
select 2, -1;
select dim, sum(val)
from #T
group by dim with rollup;
Rollup counted the negative value correctly.
Can you provide the table and values that aren't working?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 12:03 pm
GSquared (9/28/2009)
I just tried this and got the right results:
create table #T (ID int identity primary key,
Dim int,
Val int);
insert into #T (Dim, Val)
select 1, 1 union all
select 1, 2 union all
select 2, 1 union all
select 2, -1;
select dim, sum(val)
from #T
group by dim with rollup;
Rollup counted the negative value correctly.
Can you provide the table and values that aren't working?
Hrmm, you are right. I manually added/subtracted all my numbers and it appears to be correct. Something else is wrong. This is my table structure:
CREATE TABLE [dbo].[CHARGECREDIT](
[CHARGECREDITNUMBER] [int] NOT NULL,
[CHARGE_CREDIT_CODE] [varchar](10) ,
[CHARGE_CREDIT_TYPE] [varchar](1),
[AMOUNT] [numeric](18, 6) NULL,
[ENTRY_DATE] [datetime] NULL,
[SUMMARY_TYPE] [varchar](6)
)
And this is my code:
select cc.summary_type as 'Summary Type'
,case datepart(month,cc.entry_date) When 1 then 'January'
When 2 then 'February'
When 3 then 'March'
When 4 then 'April'
When 5 then 'May'
When 6 then 'June'
When 7 then 'July'
When 8 then 'August'
When 9 then 'September'
When 10 then 'October'
When 11 then 'November'
When 12 then 'December'
End as 'Month'
,cc.charge_credit_code as 'Charge/Credit Code'
,sum(cc.amount) as 'Totals'
from chargecredit as cc
where entry_date between '07/01/2008' and '06/30/2009'
group by summary_type
,datepart(month,entry_date)
,cc.charge_credit_code with rollup
order by datepart(month,entry_date)
,cc.charge_credit_code
Basically what I want to end up with is complete summarization by summary type, then month, then by charge/credit code, then of course the totals. My current code pulls what *appears* to be correct. But Im not 100% its the best way to go about it. Im sorry but I dont know how to provide test data to insert into the table.
September 28, 2009 at 12:08 pm
That looks correct.
Test data would just be a matter of posting an insert statement with a few rows (5 or 10 is usually enough).
Generally, I leave that kind of summary data up to the presentation layer. Reporting Services, Access Reports, Crystal Reports, et al, are more flexible and easier to deal with than building it into SQL. But, assuming you need it in the query, it looks correct to me. Though, from your description, it almost sounds like "with Cube" might be more appropriate than "with Rollup".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2009 at 11:42 am
craig-404139 (9/28/2009)
Im sorry but I dont know how to provide test data to insert into the table.
See the following for an easy way to do it...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply