Post reply

query

  • November 28, 2012 at 11:31 pm

    #285405

    I am having query

    select distinct(cetshnumber), INVOICEITEMS.ItemQuantity 'Quantity Manufactured',INVOICEITEMS.ItemQuantity 'Quantity Cleared'

    from INVOICEITEMS,invoice,cetsh where invoice.invoicenumber=invoiceitems.invoicenumber and

    month(InvoiceDate)=11 and year(InvoiceDate)=2012 group by cetshnumber,INVOICEITEMS.ItemQuantity

    I have given distinct but still the cetshnumber value is repeated for each record.

    If I use distinct(field1),i have to use groupby field1 to get result .i cannot add itemquantity in select clause and if i want to add itemquantity, again i have to add in group by itemquantity

    Any other possible solution?

    Thanks and Regards

    N.Ram

  • November 29, 2012 at 12:00 am

    #1563569

    Distinct is not a function. It does not take parameters. It does not give you distinct of a column that you put brackets around. It gets you distinct rows.

    For better understanding, correct how it's written (and fix the joins):

    select distinct

    cetshnumber, INVOICEITEMS.ItemQuantity 'Quantity Manufactured',INVOICEITEMS.ItemQuantity 'Quantity Cleared'

    from INVOICEITEMS inner join invoice ON invoice.invoicenumber=invoiceitems.invoicenumber

    CROSS JOIN cetsh

    where month(InvoiceDate)=11 and year(InvoiceDate)=2012

    group by cetshnumber, INVOICEITEMS.ItemQuantity

    Now you can see you're missing a join clause, which is probably why you have duplicate rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply