MySQL Forums
Forum List  »  Newbie

Group Balance Results, Then Count All the Results
Posted by: Mike Tompson
Date: September 29, 2015 11:55AM

I'm new to SQL and 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 function, 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
Select CREDIT_LIMIT, Count(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
Select CREDIT_LIMIT
From Customer
group by CREDIT_LIMIT) Customer;

Results
Count(*)
3

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 CREDIT_LIMIT, Count(CREDIT_LIMIT)
From
(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
Mike

Options: ReplyQuote


Subject
Written By
Posted
Group Balance Results, Then Count All the Results
September 29, 2015 11:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.