August 1, 2008 at 4:56 am
i am suming a column which has exponential values in it. Sum is happening only for the records which are not having exponential character in it. Records with exponential values are gettintg ignored by the aggregate function sum. Could anyone please guide me how i can make sum to include values with exponential characters as well.
August 1, 2008 at 5:04 am
HI Kisha,
Please could you provide us with some sample data, and datatype definitions?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2008 at 5:18 am
I am creating table #RPT1 as shown in query 1. Later i am populating it with values. The column we are going to discuss is CTFCSTTE_D. After loading the table, i am creating a new table #RPT3 from #RPT1 as shown in query2.
At this point since i am suming the values, the data range for column CTFCSTTE_D is exceeding what is specified for few records as a result they are being show as exponential characters. So the exponential values like 2.9999999999999997E-4 in column CTFCSTTE_D are neglected by the aggregate function SUM - sqlserver.
I want someway through which i can include these exponential values as well in the SUM.
Query 1:
CREATE TABLE #RPT1
(
MEDIA_YEAR SMALLINT,
MEDIA_CODE VARCHAR(20),
MEDIA_TYPE_ID SMALLINT ,
DIV VARCHAR(20) ,
DEPT INT DEFAULT 0,
CLASS_NO SMALLINT,
CLASS_DESC varchar(41) DEFAULT 'Unassigned',
COLLECTION varchar(41) DEFAULT 'Unassigned',
PRDTPY VARCHAR(20) DEFAULT 'Unassigned',
SKU VARCHAR(10) ,
SKU_MEDIA_PRICE FLOAT,
ATD_U FLOAT DEFAULT 0,
ATD_D FLOAT DEFAULT 0,
POSDTD_U FLOAT DEFAULT 0,
POSDTD_D FLOAT DEFAULT 0,
CTFCSTTE_U FLOAT DEFAULT 0,
CTFCSTTE_D FLOAT DEFAULT 0,
PTFCSTTE_U FLOAT DEFAULT 0,
PTFCSTTE_D FLOAT DEFAULT 0,
ORIGPLN_U FLOAT DEFAULT 0,
ORIGPLN_D FLOAT DEFAULT 0,
FNLPLN_U FLOAT DEFAULT 0,
FNLPLN_D FLOAT DEFAULT 0
)
Query 2:
SELECT
T1.MEDIA_YEAR,T1.MEDIA_TYPE_ID,
LTRIM(RTRIM(T1.DIV)) DIV,LTRIM(RTRIM(T1.DEPT)) DEPT,
T1.CLASS_NO,LTRIM(RTRIM(T1.CLASS_DESC)) CLASS_DESC,
LTRIM(RTRIM(T1.COLLECTION)) COLLECTION,T1.PRDTPY,
T1.SKU,SUM(ATD_U)ATD_U,SUM(ATD_D) ATD_D,SUM(POSDTD_U) POSDTD_U,SUM(POSDTD_D) POSDTD_D ,
SUM(CTFCSTTE_U) CTFCSTTE_U ,SUM(CTFCSTTE_D) CTFCSTTE_D,
SUM(PTFCSTTE_U) PTFCSTTE_U ,SUM(PTFCSTTE_D) PTFCSTTE_D,
SUM(ORIGPLN_U) ORIGPLN_U,SUM(ORIGPLN_D) ORIGPLN_D,
SUM(FNLPLN_U) FNLPLN_U ,SUM(FNLPLN_D) FNLPLN_D
into #RPT3
FROM #RPT1 T1
group by T1.MEDIA_YEAR, T1.MEDIA_TYPE_ID,
LTRIM(RTRIM(T1.DIV)),LTRIM(RTRIM(T1.DEPT)),
T1.CLASS_NO,LTRIM(RTRIM(T1.CLASS_DESC)),
LTRIM(RTRIM(T1.COLLECTION)),T1.PRDTPY,
T1.SKU
August 1, 2008 at 5:29 am
How are you getting the data in to the table with the expo values ?
Could you give me an example of an insert line of data thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2008 at 5:51 am
I simply created a table with a float column and inserted the records in it as
INSERT INTO Tab2 (ff)
SELECT (0.003 / 100000) FROM Tally a ,Tally b
WHERE a.N <= 100
And than ran ther following query
SELECT Sum(ff) FROM Tab2
Got No Error...
August 1, 2008 at 5:58 am
It will be very convinient if you post the sample data
Atif SHeikh
August 1, 2008 at 6:07 am
I agree, I can't get any errors ,so I'm curious about the data more than anything else
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply