Query returns different values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query returns different values

Author

Topic

ramachandran.123
Starting Member

3 Posts

Posted - 2012-12-10 : 04:18:09

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-10 : 06:21:00

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources