Results 1 to 3 of 3

Thread: Query doubt

  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: Query doubt

    Hello,

    select vendorid, vendorname,sum(invoiceamt)as [spend],count(*) as ordercount, case when invoiceamt<0 then cast(sum(invoiceamt) AS decimal(20,1))else '0.00' end as credits, avg(x) as avgx from invoice
    group by vendorid, vendorname

    this is wat m trying but I am getting an error as I am not putting invoiceamount in the group by clause which is obvious; but i dont want to include invoice amount in group by clause. I just want the query to include vendor id and vendorname in group by clause.

    Please help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT vendorid
         , vendorname
         , SUM(invoiceamt) AS [spend]
         , COUNT(*) as ordercount
         , SUM(CASE WHEN invoiceamt < 0 
                    THEN invoiceamt 
                    ELSE NULL END) AS credits
         , AVG(x) AS avgx 
      FROM invoice 
    GROUP 
        BY vendorid
         , vendorname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    10
    thanks a lot!!! it worked...

Posting Permissions

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