You must Sign In to post a response.
  • Category: SQL Server

    In sql server how to compare two columns values are equal or not equal?

    Friends,

    I have one table in my sql server. the table name is Inward_Material. The table have two columns namely ID, REF_ID. The columns have some value like

    ID REF_ID
    INM1 PAY1
    INM2 INM2
    INM3 INM3


    I want to calculate sum if ID and REF_ID are equal otherwise not calculate sum. So my doubt is how to compare two columns values are equal or not equal?
  • #743774
    Hi,
    You can user below query to find which are all the rows in which ID and REF_ID are equal,
    select * from Inward_Material where ID = REF_ID

    Is the sum being calculated is of this table columns or is a different table?

    Cheers,
    Vignesh Kannan

  • #743775
    Hi,

    You can use CASE statement for this requirement
    example code



    select case
    when column1=column2 then calculate sum here
    else
    'return required value'
    end as yourresult
    from tablename


    Regards
    Sekhar Babu,
    www.aspdotnet-sekhar.blogspot.in

  • #743777
    hi,

    use this query
    select sum(columnname) from tablename where namely ID= REF_ID

    Hope this helps you regards
    Shalini

  • #743782
    If you want to calculate the some of the row where values are equal then



    Select Sum(Price) where Id = refID

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #743783
    Hi Nepolian,

    We can use where clause to compare two columns.
    Otherwise case statements are used to compare two values.

    See the below example quries

    select ID,REF_ID,SUM(Amount)Amount from Inward_Material where ID=REF_ID group by ID,REF_ID

    select sum(case when ID=REF_ID then Amount Else 0 End)Amount from Inward_Material


    You can use the query based on your requirement.

    Still you not get your output revert back with examples.

  • #743786
    Hi,
    As you are aware you cannot sum up the non numeric fields. If you wanted to know the count then use group by and count together to get the difference.
    select count(ID) from tablename group by ID


    Regards,
    Asheej T K

  • #743787
    Hi,

    Is it the sum of ID/REFID or is the sum of other value available in different table?

    IF you want to compare the values are equal or not you can do it this way if you want to display

    Select ID,REFID,Case ID when REfID then 'equal' else
    'not equal' end as EqualNotEqual from Inward_Material

  • #743816
    Hi,

    If you want to check the columns is matched or not then use "="operator if you want to check the columns not matched case then use not equal or "<>" symbol.

    Hope this will help you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #743842
    HI..

    create table Inward_Material (Id Varchar(10),Ref_Id varchar(10))

    insert into Inward_Material values('INM1', 'PAY1'),('INM2' ,'INM2'),('INM3' ,'INM3')

    select * from Inward_Material where Id=Ref_Id

    select SUM(Id) from Inward_Material where Id=Ref_Id

  • #743892
    Hai Nepolian,
    I am not sure about your business logic and what exactly you want to achieve by this but if you want to get the sum from the same table by referring the keys in which one is primary key and other one is foreign key then you can probably make use of Self join. The self join of the table can be achieved by creating two instances of the same table and use the where clause for the comparing the ids as below:

    Select SUM(Col_Name)
    FROM Inward_Material a, Inward_Material b
    WHERE a.ID = b.REF_ID

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


  • Sign In to post your comments