asked on

SQL 2008 R2

I have a table called INSPECTION.

In that table I have the following columns:
,[APPLICANT]
,[LOCATION]
,[REMARKS]
,[QUANTITY]
,[COUNT]
,[CONTAINER]
,[GRADE]
,[CERTIFICATE NUMBER]
,[CAR NUMBER]
,[QUANTITY THOUSAND WEIGHT]
,[TIME INSPECTION BEGUN]
,[TIME INSPECTION COMPLETED]

I would like to create a column or view or table that would add the numbers together from the QUANTITY THOUSAND WEIGHT column if the number in the CERTIFICATE NUMBER are the same.

Example:

73496            144550
73496            6300
73496            4250
73496            58800
73496            68700
73496            6500
73496            5952
73496            40000
73496            1600
73496            6420
73496            347800
73496            53500
73498            90850
73498            25000
73498            20000
73498            7760
73498            57750
73498            70950
73498            10850
73498            348500
73498            5600
73498            12600

I would now have a column called CERTIFICATE NUMBER.

In that column I would only see the certificate number 73496 once with the total of 744372, and certificate number 73498 once with a total of 649860.

Is this doable??

virtuadept

SELECT [CERTIFICATE NUMBER]
,SUM([QUANTITY THOUSAND WEIGHT]) as SUM_QTY_THOUS_WT
FROM INSPECTION
GROUP BY [CERTIFICATE NUMBER]

Aneesh

>I would now have a column called CERTIFICATE NUMBER
Is this another table , otherwise
Why do you want to have this column in the table ? You already have data and you can get this info using the above query 'virtuadept' provided; or else you can create a view with same query; I dont really recommend storing the

Curtis Long

ASKER

When I try to run that query I get the error:
Executed SQL statement: SELECT [CERTIFICATE NUMBER]
,SUM([QUANTITY THOUSAND WEIGHT]) as SUM_QTY_THOUS_WT
FROM INSPECTION
GROUP BY [CERTIFICATE NUMBER]
Error source: .Net SQL Client Data Provider
Error message: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Curtis Long

ASKER

Certificate number is TEXT and I need it to be numeric??

Curtis Long

ASKER

Yes, I found this to be true.

Thanks for your help ALL!! :-)

virtuadept

Yes. You can either change the table definition to not be TEXT or you could also do this:

SELECT CONVERT(VARCHAR(MAX),[CERTIFICATE NUMBER]) as Certificate_Number
,SUM([QUANTITY THOUSAND WEIGHT]) as SUM_QTY_THOUS_WT
FROM INSPECTION
GROUP BY CONVERT(VARCHAR(MAX),[CERTIFICATE NUMBER])

Curtis Long

ASKER

For this view, if I wanted to add the column for the applicant and the date associated, how could I accomplish this??

Or should I post a second question for it??

Thanks!!

ASKER CERTIFIED SOLUTION

virtuadept

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

virtuadept

Also if you have TEXT columns for those you can get around with CONVERT(VARCHAR(MAX),column_name) in your SELECT and GROUP BY clauses.