Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    Unanswered: Query Returns different values

    Dear Sir

    I have written sql query


    select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
    INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
    INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate + INVOICECHARGES.ChargesValue*INVOICE.ExchangeRate as AssValue, INVOICE.ExcemptionNumber,
    Invoice.ExcemptionNotificationSerialNo, CHARGESMASTER.ChargesPercentage,INVOICECHARGES.Cha rgesValue
    From INVOICE, INVOICEITEMS, INVOICECHARGES, CETSH, INVOICETYPEMASTER,CHARGESMASTER Where
    INVOICETYPEMASTER.InvoiceTypeCode=INVOICE.InvoiceT ypeCode And cast(CETSH.CETSHNumber as varchar)=INVOICE.TarrifHeadNumber
    And INVOICECHARGES.InvoiceNumber=INVOICE.InvoiceNumber AND INVOICECHARGES.FinYearCode=INVOICE.FinYearCode AND
    INVOICECHARGES.BranchCode=INVOICE.BranchCode And INVOICECHARGES.TaxPaymentType=1 And
    CHARGESMASTER.DutyHeadCode=5 And INVOICEITEMS.InvoiceNumber=INVOICE.InvoiceNumber
    AND INVOICEITEMS.FinYearCode=INVOICE.FinYearCode AND INVOICE.BranchCode=INVOICEITEMS.BranchCode
    And (INVOICE.InvoiceTypeCode=1 or INVOICE.InvoiceTypeCode=2) And month(INVOICE.InvoiceDate)='11'
    And year(INVOICE.InvoiceDate)='2012' And INVOICE.BranchCode=1001
    Group by INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber, CETSH.GoodsDescription,
    INVOICETYPEMASTER.InvoiceTypeName, CHARGESMASTER.ChargesPercentage,INVOICECHARGES.Cha rgesValue,
    INVOICE.ExcemptionNumber,Invoice.ExcemptionNotific ationSerialNo,
    INVOICE.BasicValue, invoice.exchangerate,INVOICEITEMS.ItemQuantity Order by INVOICE.InvoiceTypeCode desc,
    INVOICE.TarrifHeadNumber

    I am getting 2 records

    2 85433000 85433000 DOMESTIC 2.00 98000.00 98000.0000 010/1997 DT: 01.03.1997 1 12.00 .00

    2 85433000 85433000 DOMESTIC 1.00 230000.00 230000.0000 010/1997 DT: 01.03.1997 1 12.00 .00

    Amount is 328000 and quantity 3
    If i use sum by in sql server

    like

    select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
    INVOICETYPEMASTER.InvoiceTypeName, sum(INVOICEITEMS.ItemQuantity) as SumQuantity,
    sum(INVOICE.BasicValue * INVOICE.ExchangeRate + INVOICECHARGES.ChargesValue*INVOICE.ExchangeRate) as AssValue, INVOICE.ExcemptionNumber,
    Invoice.ExcemptionNotificationSerialNo, CHARGESMASTER.ChargesPercentage,INVOICECHARGES.Cha rgesValue
    From INVOICE, INVOICEITEMS, INVOICECHARGES, CETSH, INVOICETYPEMASTER,CHARGESMASTER Where
    INVOICETYPEMASTER.InvoiceTypeCode=INVOICE.InvoiceT ypeCode And cast(CETSH.CETSHNumber as varchar)=INVOICE.TarrifHeadNumber
    And INVOICECHARGES.InvoiceNumber=INVOICE.InvoiceNumber AND INVOICECHARGES.FinYearCode=INVOICE.FinYearCode AND
    INVOICECHARGES.BranchCode=INVOICE.BranchCode And INVOICECHARGES.TaxPaymentType=1 And
    CHARGESMASTER.DutyHeadCode=5 And INVOICEITEMS.InvoiceNumber=INVOICE.InvoiceNumber
    AND INVOICEITEMS.FinYearCode=INVOICE.FinYearCode AND INVOICE.BranchCode=INVOICEITEMS.BranchCode
    And (INVOICE.InvoiceTypeCode=1 or INVOICE.InvoiceTypeCode=2) And month(INVOICE.InvoiceDate)='11'
    And year(INVOICE.InvoiceDate)='2012' And INVOICE.BranchCode=1001
    Group by INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber, CETSH.GoodsDescription,
    INVOICETYPEMASTER.InvoiceTypeName, CHARGESMASTER.ChargesPercentage,INVOICECHARGES.Cha rgesValue,
    INVOICE.ExcemptionNumber,Invoice.ExcemptionNotific ationSerialNo Order by INVOICE.InvoiceTypeCode desc,
    INVOICE.TarrifHeadNumber

    2 85433000 85433000 DOMESTIC 9.00 984000.0000 010/1997 DT: 01.03.1997 1 12.00 .00

    I am getting different amount 984000.0000 and quantity 9.

    Please help me to fix the issue

    Thanks and Regards
    N.Ram

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    First, let's get the horrible formatting off the table:
    Code:
    select INVOICE.InvoiceTypeCode, 
    	INVOICE.TarrifHeadNumber,
    	CETSH.GoodsDescription,
    	INVOICETYPEMASTER.InvoiceTypeName, 
    	INVOICEITEMS.ItemQuantity as SumQuantity,
    	INVOICE.BasicValue ,
    	INVOICE.BasicValue * INVOICE.ExchangeRate + INVOICECHARGES.ChargesValue*INVOICE.ExchangeRate as AssValue, 
    	INVOICE.ExcemptionNumber,
    	Invoice.ExcemptionNotificationSerialNo, 
    	CHARGESMASTER.ChargesPercentage,
    	INVOICECHARGES.Cha rgesValue
    From INVOICE, 
    	INVOICEITEMS, 
    	INVOICECHARGES, 
    	CETSH, 
    	INVOICETYPEMASTER,
    	CHARGESMASTER 
    Where INVOICETYPEMASTER.InvoiceTypeCode=INVOICE.InvoiceTypeCode 
      And cast(CETSH.CETSHNumber as varchar)=INVOICE.TarrifHeadNumber
      And INVOICECHARGES.InvoiceNumber=INVOICE.InvoiceNumber 
      AND INVOICECHARGES.FinYearCode=INVOICE.FinYearCode 
      AND INVOICECHARGES.BranchCode=INVOICE.BranchCode 
      And INVOICECHARGES.TaxPaymentType=1 
      And CHARGESMASTER.DutyHeadCode=5 
      And INVOICEITEMS.InvoiceNumber=INVOICE.InvoiceNumber
      AND INVOICEITEMS.FinYearCode=INVOICE.FinYearCode 
      AND INVOICE.BranchCode=INVOICEITEMS.BranchCode
      And (INVOICE.InvoiceTypeCode=1 or INVOICE.InvoiceTypeCode=2) 
      And month(INVOICE.InvoiceDate)='11'
      And year(INVOICE.InvoiceDate)='2012' 
      And INVOICE.BranchCode=1001
    Group by INVOICE.InvoiceTypeCode, 
    	INVOICE.TarrifHeadNumber, 
    	CETSH.GoodsDescription,
    	INVOICETYPEMASTER.InvoiceTypeName, 
    	CHARGESMASTER.ChargesPercentage,
    	INVOICECHARGES.Cha rgesValue,
    	INVOICE.ExcemptionNumber,
    	Invoice.ExcemptionNotificationSerialNo,
    	INVOICE.BasicValue, 
    	invoice.exchangerate,
    	INVOICEITEMS.ItemQuantity 
    Order by INVOICE.InvoiceTypeCode desc, INVOICE.TarrifHeadNumber
    
    
    select INVOICE.InvoiceTypeCode, 
    	INVOICE.TarrifHeadNumber,
    	CETSH.GoodsDescription,
    	INVOICETYPEMASTER.InvoiceTypeName, 
    	sum(INVOICEITEMS.ItemQuantity) as SumQuantity,
    	sum(INVOICE.BasicValue * INVOICE.ExchangeRate + INVOICECHARGES.ChargesValue*INVOICE.ExchangeRate) as AssValue, 
    	INVOICE.ExcemptionNumber,
    	Invoice.ExcemptionNotificationSerialNo, 
    	CHARGESMASTER.ChargesPercentage,
    	INVOICECHARGES.ChargesValue
    From INVOICE, 
    	INVOICEITEMS, 
    	INVOICECHARGES, 
    	CETSH, 
    	INVOICETYPEMASTER,
    	CHARGESMASTER 
    Where INVOICETYPEMASTER.InvoiceTypeCode=INVOICE.InvoiceTypeCode 
      And cast(CETSH.CETSHNumber as varchar)=INVOICE.TarrifHeadNumber
      And INVOICECHARGES.InvoiceNumber=INVOICE.InvoiceNumber 
      AND INVOICECHARGES.FinYearCode=INVOICE.FinYearCode 
      AND INVOICECHARGES.BranchCode=INVOICE.BranchCode 
      And INVOICECHARGES.TaxPaymentType=1 
      And CHARGESMASTER.DutyHeadCode=5 
      And INVOICEITEMS.InvoiceNumber=INVOICE.InvoiceNumber
      AND INVOICEITEMS.FinYearCode=INVOICE.FinYearCode 
      AND INVOICE.BranchCode=INVOICEITEMS.BranchCode
      And (INVOICE.InvoiceTypeCode=1 or INVOICE.InvoiceTypeCode=2) 
      And month(INVOICE.InvoiceDate)='11'
      And year(INVOICE.InvoiceDate)='2012' 
      And INVOICE.BranchCode=1001
    Group by INVOICE.InvoiceTypeCode, 
    	INVOICE.TarrifHeadNumber, 
    	CETSH.GoodsDescription,
    	INVOICETYPEMASTER.InvoiceTypeName, 
    	CHARGESMASTER.ChargesPercentage,
    	INVOICECHARGES.ChargesValue,
    	INVOICE.ExcemptionNumber,
    	Invoice.ExcemptionNotificationSerialNo 
    Order by INVOICE.InvoiceTypeCode desc, INVOICE.TarrifHeadNumber
    Now, take a look at the group by clauses. See a difference?

Posting Permissions

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