Results 1 to 2 of 2

Thread: query

  1. #1
    Join Date
    Nov 2012

    Unanswered: query

    Dear Sir
    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

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by View Post
    I have given distinct but still the cetshnumber value is repeated for each record.
    DISTINCT is ~not~ a function

    putting the first column that comes after DISTINCT in parentheses does not alter how DISTINCT actually works -- it applies to all columns in the SELECT list

    if you want only 1 result row for each cetshnumber, you will have to use GROUP BY and apply aggregate functions to other columns in the SELECT list --
    SELECT cetshnumber
         , SUM(invoiceitems.itemquantity) AS 'quantity manufactured'
         , ...
      FROM invoice
      JOIN invoiceitems
        ON invoiceitems.invoicenumber = invoice.invoicenumber
      JOIN cetsh 
        ON ??
     WHERE invoice.invoicedate >= '2012-11-01'
       AND invoice.invoicedate  < '2012-12-01' 
        BY cetshnumber | @rudydotca
    Buy my SitePoint book: Simply SQL

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