Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Using SUM and performing subtraction on columns

    I need to do SUM on a column values and then subtract it from the SUM of values on a column from another table. I am using SQL server Management Studio, but getting the errors on the following Query

    SELECT note_id, cap_int_amt)- SUM (ttl_cap_int_amt) as SUM1,
    sum(orig_fee_amt)-sum(ttl_qualfyng_fee_amt) as SUM2
    FROM [spstrd00_starrpt].[dbo].[rpt23t]
    where [rpt20t].note_id =[rpt23t].note_id


    ERROR Message
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.rpt23t'.
    Msg 4104, Level 16, State 1, Line 7
    The multi-part identifier "rpt20t.note_id" could not be bound.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'ttl_qualfyng_fee_amt'.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you think the error messages are telling you?

    frankly the posted SQAL is, lets be charitable 'odd'
    there is no opening bracket on cap_int_amt, presumaby it should read
    Code:
    sum (cap_int_amt)
    [rpt20t].note_id is never defined, well rpt20t, presumably this fragement of SQL has been stolen form another query which defined a join

    Im not that familiar with SQL Server but [spstrd00_starrpt].[dbo].[rpt23t] may well be garbage. I know in some SQL databases its possible to qualify the database.table.column, but I don't know if SQL Server allows it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    moved from MySQL thread to SQL server thread
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by healdem View Post
    Im not that familiar with SQL Server but [spstrd00_starrpt].[dbo].[rpt23t] may well be garbage. I know in some SQL databases its possible to qualify the database.table.column, but I don't know if SQL Server allows it.
    Yes, see Transact-SQL Syntax Conventions (Transact-SQL) under the Multipart Names section. You can specify [server].[database].[schema].[table].[column] because tables and views can be extended to include the column name as a SQL Object.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Abha,

    You gave us a problem that we can never solve. You are the only one who can check in what database those tables are and the correct names of the columns in those tables.

    I made an attempt to correct the most obvious problems in the given code. I made some assumptions that may be totally wrong. You will have to tweak this code further to address the errors that this code will still generate.
    Code:
    SELECT rpt20t.note_id, 
    	SUM(cap_int_amt) - SUM(ttl_cap_int_amt) as SUM1,
    	SUM(orig_fee_amt) - SUM(ttl_qualfyng_fee_amt) as SUM2
    FROM spstrd00_starrpt.dbo.rpt23t as rpt23t
    	INNER JOIN spstrd00_starrpt.dbo.rpt20t as rpt20t ON
    		rpt20t.note_id = rpt23t.note_id
    GROUP BY rpt20t.note_id
    Invalid object name 'dbo.rpt23t'.
    This means the table rpt23t could not be found in the database spstrd00_starrpt. Ask the person who manages the database, (application owner, DBA, programmer, ...) where this table is located.

    The multi-part identifier "rpt20t.note_id" could not be bound.
    This means the table rpt20t was not specified in the FROM part and so the column note_id can not be found. I added spstrd00_starrpt.dbo.rpt20t as rpt20t in an attempt to solve this. As the table rpt23t could not be found in the database spstrd00_starrpt, the chance that rpt20t will be in it is slim.

    Invalid column name 'ttl_qualfyng_fee_amt'.
    This means the column ttl_qualfyng_fee_amt could not be found in any of the tables specified in the FROM part. Most likely this is a column of the rpt20t table. When you can get the spstrd00_starrpt.dbo.rpt20t as rpt20t part correct this error may go away.


    Seek assistance from somebody who can help you find the database(s) of those tables and get you started with SQL queries. Once you have a few SQL code examples to start from, extending those later will be easier.
    Last edited by Wim; 09-05-13 at 07:16.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •