SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query returns different values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramachandran.123
Starting Member

India
3 Posts

Posted - 12/10/2012 :  04:18:09  Show Profile  Reply with Quote
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.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,
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.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

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

Edited by - ramachandran.123 on 12/10/2012 04:21:49

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/10/2012 :  06:21:00  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Your first query probably has more than one row per group.
You have duplicates - so there are actually many rows with the same quantity which appears as one row.
In the second they are summed.

Try putting a count(*) at the end of both queries.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000