Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Aggeragate of distinct records

    Hi,

    Does any one know how to do average of a column but only for distinct records. I know you can use the distinct clause but I want the distinct not on the aggregate but on another column. EG. I have a table called sales and it has a column called sales_amt and another column called reference. It may look like this:

    Code:
    reference    sales_amnt
         1      100
         1      100
         2      150
         3      150
         4      100
         4      100
    I want an average of sales_amnt but only for distinct reference numbers.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    I want an average of sales_amnt but only for distinct reference numbers.
    basic GROUP BY query, dude
    Code:
    SELECT reference
         , AVG(sales_amt) AS avg_sales
      FROM sales
    GROUP
         BY reference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess what ozzii meant was something like
    Code:
    select avg(sales_amt) from (
     select distinct reference, sales_amt from sales
    ) t
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by n_i View Post
    I guess what ozzii meant was something like
    Code:
    select avg(sales_amt) from (
     select distinct reference, sales_amt from sales
    ) t
    yes this is what i wanted. Thanks

Posting Permissions

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