crosstab to count values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

crosstab to count values

Author

Topic

mrosier
Starting Member

7 Posts

Posted - 2009-07-08 : 12:34:48

Hi Folks!
This is my first post into the forums, I just joined this site. I have a simple need I hope. I need to get a crosstab that counts distinct values in my master client table by referencing another table. Here are the details. In my master table Clients, each record is a client and one field is called Zip for their zip codes. Now I have another table I created called Zip which contains a list of zip codes and nothing else, just that one field. I want to make a crosstab that will count how many times each zip code in the Zip table appears in the Client table thereby counting how many clients reside in each zip code in my Zip table. Can someone help me write the SQL query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 12:38:07

[code]SELECT z.zipcode,COUNT(DISTINCT c.client)
FROM Zip z
JOIN Clients c
ON c.Zip=z.zipcode
GROUP BY z.zipcode
[/code]

mrosier
Starting Member

7 Posts

Posted - 2009-07-08 : 14:20:36

well I tried to modify to fit the script you sent, but let me tell you table and column names exactly. First I have the master table Clients with the field Zip. Then I have the table Zip with the field Zips. The table Client table has many fields but these of course are the fields of interest. Can you tell me about your first line? I think that is my problem when I hit the Count() command. Sorry for the hassle, it is just I am still obviously new to working with SQL. Thanks so much for the help :)!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 14:22:48

[code]SELECT z.zips,COUNT(DISTINCT c.client)
FROM Zip z
JOIN Clients c
ON c.Zip=z.zips
GROUP BY z.zips
[/code]

mrosier
Starting Member

7 Posts

Posted - 2009-07-08 : 15:00:13

what does c.client represent though in the first line of the query? I don't know how to match it up in my table anywhere.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:17:51

c.client represent column containing clinet info in clients table

mrosier
Starting Member

7 Posts

Posted - 2009-07-09 : 10:21:59

I am sorry, I know I am definitely being a newbie to this, but which client information? Is it supposed to be zips as well? I tried making it c.zips in the first line, but the result is a column of all the zips in the Z table and a second column with a count of 1 for each record meaning I am guessing each zip has a count of 1 as opposed to counting each instance of those zips in the client table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:25:48

ok...post your table structure with some sample data. then i will be able to give you exact query

mrosier
Starting Member

7 Posts

Posted - 2009-07-09 : 10:42:05

is there a way to email you 2 excel files? I have the tables in those. I try and past here, but it of course is all out of order and I see no upload option in this forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:43:55

no need. just post 5 rows from each. thats enough

mrosier
Starting Member

7 Posts

Posted - 2009-07-09 : 10:53:55

Great thanks! In the client table I put x's in all the cells except for the Zip column which of course corresponds to the Zip column in the Zips table. Thanks for helping me so much on this :)!

Client table-----------------------------------------------------------------------------------------------------------------------

FIRST_NAME LAST_NAME STREET_ADDRESS STREET_ADDRESS_2 CITY STATE ZIP ZIP9 PHONE_NUMBER AFN_IND BB_SEG BB_SUPER_SEG SYNTH_ALL_HOUSE_KEY SYSPRIN_CORP REGION_NAME ANALOG_ACTIVE_IND0 DIGITAL_ACTIVE_IND0 VOICE_ACTIVE_IND0 HSI_ACTIVE_IND0 CDV_IND0 CONT_MNTHS_ACTIVE_TENURE VOICE_MOS_SVC AREA_NAME GENERAL_LEDGER_NAME CSG ACCOUNT NUMBER DST ACCOUNT NUMBER DIVISION FTA ID pulldate Randval Analog digital HSI CDV AreaID RegionID GLID GLIndex
xxxx xxxx xxxx xxxx xxxx xxxx 30188 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30144 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30188 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30188 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30114 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30152 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30135 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx xxxx 30135 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx

Zips Table-------------------------------------------------------------------------------------------------------------------------

Zip
07722
02636
98455
22046
94575
02222
20892
06022
-----------------------------------------------------------------------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:56:37

[code]SELECT z.Zip,COUNT(DISTINCT COALESCE(c.FIRST_NAME + ' ','') + COALESCE(c.LAST_NAME,''))
FROM Zips z
JOIN Client c
ON c.Zip=z.Zip
GROUP BY z.Zip
[/code]

mrosier
Starting Member

7 Posts

Posted - 2009-07-09 : 11:06:40

why are we coalescing first name and last name though? Those might not be unique in the table (I know it should be, but it might not be). Randval and synth_all_house_key together make up the primary key in case that makes a difference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources