Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Group Balance Results, Then Count All the Results

    This may be a long shot since I just noticed there are many unanswered post in this forum, but my question should be fairly simple since I'm new to SQL. I'm just in the 4th chapter of my Book: Learning SQL. In this chapter they discuss sub queries, nesting queries and grouping.

    So I started playing around with some of the examples they provided and made changes here and there for practice. Well, I've seem to hit my first challenge and it's driving me nuts. Maybe it's more advanced than Ch.4 and I need to move on to Ch.5. One of the examples showed me how to group, so I thought what if I wanted to count all of the groups after the fact. I assumed a nested query would be required here and the Count command, but I can't seem to make it work. I did manage to write two individual queries that give the results of each, but I don't know how to make them coexist in one query. I'm thinking it has something to do with using Group By with sub queries, because I have no trouble using sub queries that don't include grouping.

    Anyhow, here are the two queries:

    #Part 1, Results Grouped By Credit_Limit
    From Customer
    group by CREDIT_LIMIT

    Results :
    Credit_Limit Count(Credit_Limit)
    5000 2
    7500 6
    10000 2

    I would like it to also show the count of all the results, which would be 3.

    I wrote the following SQL that seems to accomplish this:

    #Part 2: Counts all of the results from Part 1
    Select Count(*) From
    From Customer
    group by CREDIT_LIMIT) Customer;


    I have tried multiple ways within my limited knowledge to get these to work together.
    It seems the obvious would be to put the Group By clause in the inner sub query, and then have the outer primary query count the total results. Yet every time I try to write something to this effect, I receive errors.

    Here is an example of one of my attempts:

    (Select Count(*)
    From Customer
    group by CREDIT_LIMIT) Customer;

    For this one, I get Error Code 1054: Unknown column 'CREDIT_LIMIT' in 'fieldlist'.
    I don't understand how the CREDIT_LIMIT field can be considered unknown. It is in the Customer table, and it is spelled correctly.

    Any suggestions?

    Thank you

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    There are many ways to do the same. You can use your application program to count the rows returned. take a look at roll up functions. How do expect the results to look when they are returned?

Posting Permissions

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