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

    Unanswered: Query returns more records than expected

    Dear Sir

    I am having a query

    select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
    invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
    month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
    group by INVOICE.TarrifHeadNumber

    tarrifheadno SUM(INVOICEITEMS.ItemQuantity)

    84195030 9.00
    84198910 5.00
    84212190 223.00
    84569090 247.00
    84799040 1138.00
    8481-80-1030 137.00
    85433000 6177.20

    tarrifheadno is unique

    now if i use below query and add invoicetypecode field



    select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
    invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
    month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
    cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode

    This query return distinct of 11 records

    84195030 84195030
    9.00 1
    84198910 84198910
    5.00 2
    84212190 84212190
    157.00 1
    84212190 84212190
    42.00 2
    84212190 84212190
    24.00 3
    84569090 84569090
    189.00 1
    84569090 84569090
    58.00 2
    84799040 84799040
    166.00 1
    84799040 84799040
    972.00 2
    85433000 85433000
    3764.00 1
    85433000 85433000
    2413.20 2

    Please help me to get same 7 records

    Thanks and Regards

    N.Ram

    Reply Quote

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Apparently the column InvoiceType have different values, so the rows are not grouped.
    Try removing the column InvoiceType of the query.

    Hope this helps.

Tags for this Thread

Posting Permissions

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