View Poll Results: SQL Average query

Voters
0. You may not vote on this poll
  • You may post attachments

    0 0%
  • You may post replies

    0 0%
  • You may edit your posts

    0 0%
  • You may edit your posts

    0 0%
Multiple Choice Poll.
Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Los Angeles, CA
    Posts
    4

    Unanswered: SQL average query

    I need your help to write a one SQL query. Following are the table name and column name.

    Table Column Name
    RA_CUSTOMER_TRX_ALL bill_to_customer_id
    cust_trx_type_id

    Now, my task is to get average number of transaction types per customer, minimum number of transaction type per customer, maximum number of transaction type.
    For example, In following table How many TRX_type used per customer?

    bill_to_customer_id cust_trx_type_id
    1041 1020
    1041 1021
    1043 1020
    1043 1021
    1043 1023
    1045 1023
    1045 1027
    1047 1020
    1050 1027
    1050 1021
    ---- ----
    ---- ----
    ---- ----
    For above example, customer 1041 has 2 transaction type used, 1043 has 3 used, I am looking for all customer average how many transaction type used.
    Average 1.25 transaction type used per customer
    Minimum 1.0 transaction type used for customer
    Maximum 4.0 transaction type used for customer

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select min(cust_trx_count) as min_cust_trx_count
         
    max(cust_trx_count) as max_cust_trx_count
         
    sum(cust_trx_count)
            / 
    count(*)         as avg_cust_trx_count
      from 
    (        
           
    select bill_to_customer_id 
                 
    ,count(*)  as  cust_trx_count
             from RA_CUSTOMER_TRX_ALL 
           group
               by bill_to_customer_id   
           
    ) as derivedtable 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Los Angeles, CA
    Posts
    4

    SQL average query

    Hello,

    Thanks for quick reply. This query calculates only transaction type's average but not by customer. What I am looking from this query is how many transaction type used per customer. Is there any other way?

    Rajen

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry

    add bill_to_customer_id to the outer SELECT

    then add GROUP BY bill_to_customer_id at the end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Los Angeles, CA
    Posts
    4

    SQL average query

    thanks again but result is not what I am looking for. Here is the result from query. Just sample,

    Bill_To_custoner_id AVG_CUST_COUNT
    1041 4
    1043 2
    1045 1
    1047 2
    --- ------

    However, I am looking for average transaction type used per customer. On above example, total 4 customer and total transaction type 9 so 9 divide by 4 customer which will be average. 2.25. Then minimu will be 1 and maximum will be 4.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select bill_to_customer_id 
         , min(cust_trx_count) as min_cust_trx_count
         , max(cust_trx_count) as max_cust_trx_count
         , sum(cust_trx_count)
            / count(*)         as avg_cust_trx_count
      from (        
           select bill_to_customer_id 
                 ,count(*)  as  cust_trx_count
             from RA_CUSTOMER_TRX_ALL 
           group
               by bill_to_customer_id   
           ) as derivedtable
    group
        by bill_to_customer_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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