By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,730 Members | 1,546 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,730 IT Pros & Developers. It's quick & easy.

SQL-Top Numbers

P: 73
Have a complicated question to ask: I have a table with 5000 rows and lists state and branch number. I am trying to run a count function (which I created as another query because didn't think I could do both in the same query) where it totals the amount of branches in each state. Which is showing me the top branches that sold insurance products.
Expand|Select|Wrap|Line Numbers
  1. SELECT a.STATE, Count(a.Branch) AS Branch Totals
  2. FROM [January Population] a
However I am trying to pull 2 results for every state (State is listed as numerical number 1-50), so theoretically this should show 100 results listed and rank them only to pull the top 2 states/branches that sold the most insurance. Is this Possible?
Feb 20 '08 #1
Share this Question
Share on Google+
3 Replies

P: 73
I didn't have the Correct SQL listed before I forgot to Group By Branch Also. Here is the updated SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT Format(a.[Month End DT],"mm/""01""/yy") AS Date_ID, a.STATE, a.Branch, Count(a.Branch) AS Branch_Totals
  2. FROM [January Population] AS a
  3. GROUP BY a.STATE, a.Branch, a.[MONTH END DT];
Is there a way that I could pull the Top 2 Branches out of every state?
Please Help...
Feb 21 '08 #2

Expert Mod 15k+
P: 31,769
Registering for when I have some time...
Feb 21 '08 #3

Expert Mod 15k+
P: 31,769
It's possible you can, but we'd have no way of knowing with the information so far supplied.

What is stored in your table (columns)?
How would you rate one branch against another? I see no reference anywhere to any figure that might be used.
Without this information there isn't really a question to answer.

I assume you're trying to group items (branches) together by month date. It's hard to guess why that would be from what we know.
If you fill in some blanks we'll see where we can get to.
Feb 22 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.