Results 1 to 5 of 5

Thread: count

  1. #1
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27

    Unanswered: count

    Below is the scenario

    Table A with 2 colums viz. CNum and Anum. Each Cnum can be associated with more than one Anum (ex: one client can have multiple accounts).

    I need a query (one query) that displays number of Cnum thats has exactly 1 account, 2 accounts, 3 accounts .....

    Thanks in advance

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27

    Smile

    Below is the scenario

    Table A with 2 colums viz. CNum and Anum. Each Cnum can be associated with more than one Anum (ex: one client can have multiple accounts).

    I need a query (one query) that displays number of Cnum thats has exactly 1 account, 2 accounts, 3 accounts .....

    Thanks in advance

  3. #3
    Join Date
    Jun 2003
    Posts
    15
    I think this is what you are asking for:

    select CNUM,count(ANUM) from table
    group by CNUM

    will return the number of accounts for each CNUM

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Lightbulb

    If I understand well, you need something like this:

    SELECT CNum, count(*) Accounts
    INTO #temp
    FROM A
    GROUP BY CNum

    SELECT Accounts, count(*) Total
    FROM #temp
    GROUP BY Accounts
    Go

    I don’t know how get the same result set with one select. With Sybase IQ will be different but with ASE I think there is no other way.

    Bye

    Sebastian

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27

    count

    Thanks for the responses. What i need to display is number of clients having 1 account, number of clients having 2 accounts etc... and i need to do this in one query. We can do this in Oracle using inline view. I guess, my only option in Sybase is using a temp table .

Posting Permissions

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