Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    5

    Unanswered: simple statistics(query help again)

    How to report a simple statistics: number of customers having exactly 1 account, 2 account, etc(in one query)

    Thanks very much for your help. This is excellent group. Thanks again

    Blair

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    What does your table(s) look like ?

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    As cvandemaele asked, impossible to help without DDL and possibly some sample inserts.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    unless I have made a typo try:

    Code:
    select customer_id
         , customer_name
         , count(*) as '# of Accounts'
      from Customer
      join Account on Customer.customer_id = Account.customer_id
     group by customer_id
         , customer_name
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    5
    We have two tables

    Table Customer:
    Customer_id Customer_name

    Table account:

    Account_no customer_id balance

    Thanks for your response

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Isn't Pauls post the answer you are looking for?

    Did you try it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to use a nested query. The inner one counts the number of accounts per customer, and the outer one counts the fequency of that value:

    select CustomerAccounts, count(*) CustomerAccountsFrequency
    from
    (select customer_id, count(*) as CustomerAccounts
    from Account
    group by customer_ID) AccountSubquery

    blindman

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    doh!

    Right you are blindman!
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    3 steps

    1). create a temp table using :

    select count(*) as customer_accounts
    into temp_customer
    from customer
    group by Customer_ID

    2). get your report of number of customers having 1,2....n accounts using :

    select customer_accounts , count(customer_accounts) as nbr_of_customers
    from temp_customer
    group by customer_accounts

    3). delete your temp table using :

    drop table temp_customer

  10. #10
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Post

    typo in the first step, it should be

    1). create a temp table using :

    select count(*) as customer_accounts
    into temp_customer
    from account
    group by Customer_ID

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The code I submitted does this all in one step, with no temp table.

    blindman

Posting Permissions

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