PLease help with a count problem

Question

PLease help with a count problem

emdavies82

SSC Enthusiast

Points: 128
More actions
October 27, 2011 at 2:52 am

#245809

Im still learning the basics. Im trying to see in this database how many people are registered at the same address.
I have done this
SELECT COUNT(Address_Line_1) AS Expr1, Address_Line_1
FROM Pers_Static_Personnel_History
GROUP BY Address_Line_1
HAVING (COUNT(Address_Line_1) > 1)
ORDER BY COUNT(Address_Line_1)
This however is not enough as some address line 1 say Flat 1. This could be flat 1 at differnet places. So I need to add in the post code. Then count how many people have the same address line 1 with the same postcode?
Can someone please tell me how to do this?
Thanks

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

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

Add postcode aslo in the group by and select columns. That would give the result of having the same address with the same postal with the number of entries. Please let us know if you have any other issues.

emdavies82 SSC Enthusiast Points: 128 More actions · Answer 2

Thanks so much so it should be like this:

SELECT COUNT(Address_Line_1) AS EXP2, (Post_Code)AS Expr1, Address_Line_1

FROM Pers_Static_Personnel_History

GROUP BY Address_Line_1, pOST_cODE

HAVING (COUNT(Address_Line_1) > 1)

ORDER BY COUNT(Address_Line_1)

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

emdavies82 (10/27/2011)
Thanks so much so it should be like this:
SELECT COUNT(Address_Line_1) AS EXP2, (Post_Code)AS Expr1, Address_Line_1
FROM Pers_Static_Personnel_History
GROUP BY Address_Line_1, pOST_cODE
HAVING (COUNT(Address_Line_1) > 1)
ORDER BY COUNT(Address_Line_1)

Almost.

SELECT COUNT(Address_Line_1) AS EXP2

,Post_Code

,Address_Line_1

FROM Pers_Static_Personnel_History

GROUP BY Address_Line_1

,Post_Code

HAVING (COUNT(Address_Line_1) > 1)

ORDER BY COUNT(Address_Line_1)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

emdavies82 SSC Enthusiast Points: 128 More actions · Answer 4

Thanks again for your help, so I f I then wanted to know the names of the people would this be a sub query?

emdavies82 SSC Enthusiast Points: 128 More actions · Answer 5

Select Personnel_Ref

From Pers_Static_Personnel_History

where

(SELECT COUNT(Address_Line_1) AS EXP2, Post_Code, Address_Line_1

FROM Pers_Static_Personnel_History

GROUP BY Address_Line_1, Post_Code

HAVING (COUNT(Address_Line_1) > 6))

It won't run. First query runs fine but now from that query I want to extract the workers payroll refs??

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Are you getting syntax error? Check predicate syntax.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

JonFox SSCrazy Points: 2157 More actions · Answer 7

I think you're looking for something like this:

SELECT A.First_Name, A.Last_Name, A.Post_Code, A.Address_Line_1, B.NumAtThisAddress

FROM Pers_Static_Personnel_History AS A INNER JOIN

(

SELECT Post_Code,

Address_Line_1,

COUNT(Post_Code) AS NumAtThisAddress

FROM Pers_Static_Personnel_History

GROUP BY Address_Line_1,

Post_Code

HAVING (COUNT(Address_Line_1) > 1)

) AS B ONA.Post_Code = B.Post_Code AND

A.Address_line_1 = B.Address_line_1

What this is doing is creating a derived table (aliased as "B") containing all addresses that have more than one resident, along with the number of residents at that address, then joining it back against all personnel records in order to obtain the First_Name and Last_Name columns. The result is the names and addresses of everyone who shares an address with at least one other person, along with the total number of people sharing that address. Is that what you're after, approximately?