[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sum varchar

Posted on 2009-02-20
2
Medium Priority
?
593 Views
Last Modified: 2012-05-06
i have used sp_helpdb to insert into a table

now for a report query i need to do a sum on filesize.[size] which contain data like

10.50 mb
20000.00 mb

Sum aggregate operation won't take varchar data type

SELECT        MEMBER_ACCESS.DB_SERVER_NAME, COUNT(MEMBER_ACCESS.DB_SERVER_NAME) AS Expr1, filesize.[size]
FROM            MEMBER_ACCESS INNER JOIN
                         filesize ON MEMBER_ACCESS.MEMBER_ID = filesize.name COLLATE Latin1_General_CI_AS
GROUP BY MEMBER_ACCESS.DB_SERVER_NAME, filesize.[size]

Open in new window

0
Comment
Question by:James Murrell
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 23690416
try this

SELECT        MEMBER_ACCESS.DB_SERVER_NAME, COUNT(MEMBER_ACCESS.DB_SERVER_NAME) AS Expr1, Sum(Cast(Replace(filesize.[size],' mb','') as decimal) ) AS Expr2
FROM            MEMBER_ACCESS INNER JOIN
                         filesize ON MEMBER_ACCESS.MEMBER_ID = filesize.name COLLATE Latin1_General_CI_AS
GROUP BY MEMBER_ACCESS.DB_SERVER_NAME

Open in new window

0
 
LVL 31

Author Closing Comment

by:James Murrell
ID: 31549164
thanks could not see woods for the trees
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question