How Do I Calculate The Average Variable Length For A Varchar?
Feb 26, 2008
im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars
Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)
i'm suppose to find the average length for them?
i'm suppose to use that to add up to my ROW SIZE
and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array
please help me asap before 2morrow night.
Thanks!
i have a test
View 2 Replies
ADVERTISEMENT
Jun 11, 2007
ok so i have this table:
Code:
Products_Sold
priceSold - money
itemsSold - int
An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81
to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)
But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?
in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100
but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200
and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300
but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.
I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye
View 1 Replies
View Related
Nov 29, 2013
I am trying to calculate an average within a query:
coalesce(field1, 0) + coalesce(field2], 0) + coalesce(field3, 0)/Count [AVG]
It seems to be subtracting the value of field3 from the total of 3 fields at some points instead of giving me the average.Some of the results are here:
Count Field1 Field2 Field3 Total Average
===== ====== ====== ====== ===== =======
2NULL6NULL66
11NULL28134129
10NULL33NULL3333
3NULL12NULL1212
[code]....
View 4 Replies
View Related
May 27, 2015
I have a few columns: Week1, Week2, Week3, Week4, Week5
i.e.
Week1 = 5
Week2 = 0
Week3 = 10
Week4 = 7
Week5 = 0
How do I calculate the average based on the fields that are not zero?
Incorrect:
(5+0+10+7+0)/5 = 4.4
Correct:
(5+0+10+7+0)/3 = 7.3
How to let create a custom column to let the SQL know that I only want to divide by 3 instead of 5?
View 7 Replies
View Related
Apr 22, 2008
I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table. Here is my table structureCREATE TABLE patient_procedure( proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY, patient_id int NULL, surgeon_name varchar(40) NOT NULL, proc_name varchar(20) , part_name varchar(30), wth_contrast int , wthout_contrast int , wth_wthout_contrast int, xray_part varchar(60), arth_area varchar(30), others varchar(30) , cpt varchar(20) , procedure_date smalldatetime NOT NULL, mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id) REFERENCES dbo.patient_information (Patient_id)) Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.Here the problem is when the user enters these two procedures information remaining fields will store the null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. Waiting for valuable suggestions.
View 2 Replies
View Related
Sep 2, 2007
Hi,
How do I Calculate Average Leadtime...
I have a Table named "iCalls_Calls" which has 2 Columns (start_Date and Closed_Date).I need to calculate average leadtime based on the columns from this table . I have a query and i need to add this ( calculate average leadtime) to this query.
Code:
SELECT B.USER_DIVISION,B.USER_DEPARTMENT,COUNT(*) FROM iCalls_Calls A INNER JOIN iCalls_Users B on A.REQUESTOR = B.USER_ID
GROUP BY USER_DIVISION,USER_DEPARTMENT
Can anyone send me the correct query to calculate the average time ?
Thanks..
View 14 Replies
View Related
Aug 6, 2004
Hi this might be pretty simple to you guys out there but i am having issues doing it.
Please help!!!!!!!!!!
Calculate Average of a measure called DIST irrespective of any dimension the page field.
Ie. to say it should calculate the average for any doension i bring on the rwo field.
Looking forward to your help.
you can mail me at hem_k01@yahoo.com
View 6 Replies
View Related
Apr 30, 2008
I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.
Now, I'm trying to figure out what the fastest growers are. Here's the table ddl
CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.
I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.
what am I missing here?
View 10 Replies
View Related
Jan 16, 2014
How to calculate the overall average number of days taken to complete something.
The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).
Each enquiry has a enquiry_number
Sample data typically looks like:
Enquiry number - enquiry_time - complete date
1 - 01/01/2014 - 12/01/2014
2 - 01/01/2014 - 11/01/2014
3 - 01/01/2014 - 10/01/2014
4 - 01/01/2014 - 07/01/2014
5 - 01/01/2014 - 12/01/2014
6 - 01/01/2014 - 04/01/2014
etc.
What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?
View 2 Replies
View Related
Sep 11, 2013
I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.
Dataset 1:
SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(sum(AmountPaid),0)As [Amount Paid]
FROM factAdmissions
[Code] ....
Dataset2:
Select
(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'
[Code] ....
View 20 Replies
View Related
Oct 8, 2013
I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patient information for all the Nurses.
I need to calculate Number of patients per Nurse and Average number of patients per day per nurse.
We need to calculate Average Number of patients per day per nurse = Total Patients per nurse/No.of unique days they worked
I need my report as like this.
DistinctNurse No.ofPatients AvgNo.ofpatients PerDay
Tina 100 25
Sony 50 16.6
How to get the result as above.
View 11 Replies
View Related
Oct 30, 2015
Col1 Col2 Col3 Col4
54763.00 21 0 0
--Row1
59574.00 23 0 0
--Row2
64085.00 20 0 0
--Row3
0.0 0.00 0 0 0
--Row4
I'm trying to calculate Average of Col1 of above table based on below scenario:
CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3
ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4
View 7 Replies
View Related
Sep 6, 2007
Hi All,
we have collected perfmon data for a specific lun. Here is the background of the lun. The lun is Raid10 with 4 physical disks. We have problems interpreting the data. In the perfmon counter screen we have a max of 435 and average of 0.512. Can somebody tell us what is that we are missing? Any help is greatly appreciated.
Thanks,
Venkat.
View 3 Replies
View Related
Jul 12, 2004
i have problem regarding the row length and varchar.
my problem is on every new row i have +6 more character on one of my field then the last record. and BOL says i can only have 8060 character per row.
What i can not use the full lenght of varchar(8000) on field.
Can anybody help?????????
View 5 Replies
View Related
Feb 6, 2002
Newbie question:
Why bother specifing the length in varchar()?
Why not just specify the max and not worry about truncation?
Thanks,
Martin
View 2 Replies
View Related
May 2, 2000
Hi,
I am trying to insert text into a varchar(5000) column from a JAVA program. I am using MSDE database (which I believe is a strip down version of SQL7.0), with a variation of ms SQL Server 3.70.06.90 ODBC driver and tds jdbc driver from inet software. With the ODBC driver, I don't get any error but the data saved is only 255 characters. With the latter driver, I get a data truncation error and nothing is saved.
Any help is appreciated.
Thanks
- Sharma
View 1 Replies
View Related
Nov 21, 2000
What is the max field length in SQL Server 7.0 that a varchar field can be?
I think 8000. Please advise
Thanks
View 2 Replies
View Related
Mar 9, 2007
Hi,I have a pretty straightforward question to do with variable length fields I hope someone can help me with:When using varchar (or nvarchar), is there any point in specifying a smaller length than the maximum? Does it save space or improve performance at all?ThanksRedit: I suppose the max rowsize is an issue. any others?
View 2 Replies
View Related
Aug 21, 2007
Hi all,
a simple question to ask for varchar type. I want to update a varchar type parameter to a stored procedure to update a table. My question is , if I don't specify the length. and the varchar type will be trimmed to only 1 character. I thought it's supposed to be 50 by default? Besides if I need to compare varchar values, do I need to specify length as well? Cheers.
View 9 Replies
View Related
Feb 2, 2005
Hi, all
I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when
I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share?
Thanks
Betty
View 5 Replies
View Related
May 22, 2006
Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).
Thanks for any reply.
aex
View 4 Replies
View Related
Oct 7, 2007
Hi,
I have a column set to varchar(12) so I can ensure that the length of the string entered will never be more than 12 characters but I want to limit the string to a minimum of 8 characters, so I end up with a string that is from 8-12 characters long. How do I ensure the minimum length of 8 characters? I'm using SQL Server 2005 if that helps. I tried adding a check constraint like so:
DATALENGTH(UserName) >= 8
But I keep getting an error when I save the table, so any help would be very much appreciated.
Thanks
View 3 Replies
View Related
Sep 14, 2007
Hi mates,
I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?
Thx in advance,
View 3 Replies
View Related
Oct 1, 2000
Hi all,
I have a strange situation. I have a field in the database that has to be a string type field of around 4000 characters.
So naturally I setup the field as
type: varchar
length: 4000
However when I try to put any text in this field I find that I can put no more than 1023 characters of ascii text in there.
To check if this was a max record length prob I setup a test table with only 2 fields:
ID: int, PK, Identity
longVarchar: varchar, 4000
and tried to put some ascii text into the field called longVarchar. Again the most I could put in was 1023 characters!
Thinking that it could just be that SQL svr box that was wacky, I tried it on another one with the same result.
I have tried using other field types (nvarchar, char) and have found that they all could only hold 1023 characters max, no matter what how high I defined the size of the field.
Try it out yourselves and see if you get the same result. Any useful suggestions would really be appreciated.
View 1 Replies
View Related
Dec 26, 2006
Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!
View 2 Replies
View Related
Aug 9, 2007
Hi all,
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Why is this happening and how can I prevent this?
TIA
NozFx
View 1 Replies
View Related
Dec 8, 2007
Hi all,
In my SQL Server Management Studio Express, I have a Database "testDb" that has a dbo.Inventory with a column "quantity". I executed the following sql script:
USE testDb
GO
CREATE FUNCTION AverageQuantity(@funcType varchar(20))
RETURNS numeric AS
BEGIN
DECLARE @average numeric
SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType
RETURN @averge
END
GO
I got the following error message:
Msg 137, Level 15, State 2, Procedure AverageQuantity, Line 6
Must declare the scalar variable "@averge".
I think it was declared already in the BEGIN - END block. Please tell me why I got this error and advise me how to fix this problem.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Jul 20, 2005
I am currently working on a PHP based website that needs to be able to drawfrom Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. Itook a lot of time and care creating a flexible and solid wrapper and amdeep into coding. The only problem is a noticed VARCHAR fields being drawnfrom SQL Server 2000 are being truncated to 255 characters.I searched around php.net and found the following :Note to Win32 Users: Due to a limitation in the underlying API used by PHP(MS DbLib C API), the length of VARCHAR fields is limited to 255. If youneed to store more data, use a TEXT field instead.(http://www.php.net/manual/en/functi...ield-length.php)The only problem with this advice is Text fields seem to be limited to 16characters in length, and I am having similar results in terms of truncationwith other character based fields that can store more than 255 characters.I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions andthe functions referenced here http://www.php.net/manual/en/ref.mssql.php.What are my options here? Has anybody worked around this or am I missingsomething obvious?James
View 4 Replies
View Related
Jul 10, 2007
What is the maximun length of a string a variable in SSIS can handle?
I assume it is 8000 ?
View 1 Replies
View Related
Oct 13, 2007
Hi,
we can use 'sp_executesql' to execute any statemens. I have made a search and people, seems, need the dynamic sql only to process some table/cloumn unknown in advance. My idea is that the dynamic SQL feature is ideal for passing blocks of code (aka delegates). Particularily, you may require to execute different procedures under some acquired locks. A procedure would acquire the locks, execute the code and release the locks. The problem is, however, that I cannot find the specification for the variable length parameters. It seems not feasible for SPs. Nevertheless, the 'sp_executesql itself does accept the variable number of parameters. How? Can we look at the defenition?
View 2 Replies
View Related
Oct 23, 2007
Hi,
I want to export one CSV file to a SQL table.
My problem is my Csv File is variable length file like :
000;1;amruth;20000
000;1;amruth;20000;praveen;shell;floor11
000;1;amruth;20000;praveen;shell;floor11;aaa;aa;aa;aa;aa;aa;aa
Sql Table is like col1- - - - - - - Col 15
I want to make a package to import that variable length Csv to This Sql table like
000 amruth 20000 Null Null Null Null Null Null Null Null Null Null
000 amruth 20000 Praveen shell floor11 Null Null Null Null Null Null Null
000 amruth 20000 Praveen shell floor11 aaa aa aa aa aa aa aa
I hope you got the problem.
I tried with BulkInsert using ; as column Delimiter and as Row Delimiter
and Flat File connection with Same Delimiters but its not working.
its importing like :
000 1 amruth 20000
000 1 amruth 20000;praveen;shell;floor11
000 1 amruth 20000;praveen;shell;floor11;aaa;aa;aa;aa;aa;aa;aa
its inserting like this in 4th column.
If any body can help me on this please reply Asap.
Thanks in advance
View 11 Replies
View Related
Apr 28, 1999
I'm new to SQL Server. I installed a copy of 6.5 on my server and set it up today. I received a db from a colleague and have been unable to find out what the variable types and lengths are. This may be very easy but I need to know.
Thanks,
David A. Fordinal
fordinal@uta.edu
View 1 Replies
View Related
Jun 24, 2008
Hi,
I'm trying simple code:
declare @varbinary varbinary(8000)
set @varbinary = 2592
print len( @varbinary )
number length
2590 4 bytes
2591 4 bytes
2592 3 bytes ????
2593 4 bytes
All number in range 1 - 9000 has length 4 bytes except these numbers:
32,288,544,800,1056,1312,1568,1824,2080,2336,2592,2848,3104,3360,3616,3872,4128,4384,4640,4896,5152,5408,5664,5920,6176,6432,6688,6944,7200,7456,7712,7968,8480,8736,8992
These numbers has length 3 bytes !
Why?
I´m using varbinary variable to transport ID values and parsing in loop
set @cnt = len( @binEmpIDList ) / 4 -- 4 length
....
insert into @List( ID ) values( substring( @varbinary, @i * 4 + 1, 4 ) )
...
but this algorithm malfunctioning in enumerated numbers.
Please help, how correct this.
ID values in @varbinary too much. I cannot using convert varbinary to bigint and convert bigint to string.
Dalibor
View 7 Replies
View Related
