Count of accounts per zipcode

Question

Post reply

Count of accounts per zipcode

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
December 8, 2014 at 8:11 am

#301744

Can anyone advice how to do this. I am looking to count the account number per zipcode of a state.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Try:

SELECT

State

,ZipCode

,Count(Account) AS [Accounts]

FROM YourTable

GROUP BY State, ZipCode;

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Window function version of Alvin's code

😎

SELECT

State

,ZipCode

,Count(Account) OVER

(

PARTITION BY State, ZipCode

) AS [Accounts]

FROM YourTable;

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Eirikur Eiriksson (12/8/2014)
Window function version of Alvin's code
😎
SELECT
State
,ZipCode
,Count(Account) OVER
(
PARTITION BY State, ZipCode
) AS [Accounts]
FROM YourTable;

That's probably over-complicating it for his homework. 😉

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 4

cbrammer1219

SSCarpal Tunnel

Points: 4476

December 8, 2014 at 8:43 am

#1763292

Thanks guys was what I was looking for.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

cbrammer1219 (12/8/2014)
I have that, I would like to get the totals for example, Meaning that there are that many accounts in that zipcode.
ZipCode Count
02149 20
02156 2
01234 10

I don't understand what you are asking for. As I see it, you have what you asked for.

Can you word you request differently?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

If you already have a summary of accounts by zip code, then you don't want to COUNT them, you want to SUM them instead. So use Alvin's query with a SUM instead of a COUNT function.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

pietlinden SSC Guru Points: 63436 More actions · Answer 7

But that way, the teacher will know he was cheating, because he won't be able to explain how it works.

aaron.reese SSChampion Points: 13498 More actions · Answer 8

Or use the ROLLUP option

SELECT

State

,ZipCode

,Count(Account) AS [Accounts]

FROM YourTable

GROUP BY State, ZipCode

WITH ROLLUP;

This will give a total for the State (record will be State, NULL, countTotal) and a total for the whole dataset (record will be NULL,NULL,countTotal)