how many users per town based on postcode

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

SQL Server 2000 Forums

SQL Server Development (2000)

how many users per town based on postcode

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-20 : 13:58:14

as you can see i have rsPostCode in both tables
When a new user signs up they select select only the first 4 characters of their postcode and it macthes to the ones in the pubs database using LIKE % in the SQL statement
then when logged in they have a list of all pubs in their area!

What i would like to do is show how many users are on the site based on their location eg

London (23)
Manchester (15)
Birmingham (10)
Brighton (18)

how do i do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-20 : 14:09:43

Moving topic as this doesn't belong in the Site Related Discussions forum.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-20 : 14:15:49

select pubs.Town, count(*)
from pubs
join members on pubs.PostCode=members.PostCode
group by pubs.Town

edit: fixed typos

elsasoft.org

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-20 : 14:24:55

thanks, I put that in but what ASP do i use to show the amount counted?

at the moment I have:

If Not RS1.EOF Then
     ' Records found
    %>
    <table>
        <tr>
            <td>Townname(MemberCount)</td>
        </tr>
        <%
        Do While Not RS1.EOF
            %>
                <tr>
                    <td><%=RS1("rsTown")%>(<%=RS1("MembersCount")%>)</td>
                </tr>
            <%
            RS1.MoveNext
        Loop
        %>
    </table>
    <%
Else
    ' No Records found
    Response.Write "The query: " & SQL & "<br />Delivered 0 results"
End If
%>

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-20 : 14:29:41

and should it be INNERJOIN as i am using an access database?!

I don't know how to setup my SQL server and test on IIS otherwise i'd convert my database!

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-20 : 16:27:34

can someone please help??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 17:33:35

Since the counted value has non name of the column, use

~~>(<%=RS1("MembersCount")%>)</td>~~
>(<%=RS1(1)%>)</td>

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 17:34:11

select pubs.Town, count(*)
from pubs
INNER join members on pubs.PostCode=members.PostCode
group by pubs.Town

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 07:02:36

thanks, now i get:

/pubspace/main.asp, line 10, column 36
SQL2 = "select pubs.rsTown, count(*)
-----------------------------------^

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 07:37:44

ok thanks so now I have:

select pubs.rsTown, count(*) from pubs INNER join members on pubs.rsPostCode=members.rsPostCode group by pubs.rsTown

but that only selects the exact same postcodes, i would only like to select the postcode LIKE the postcode in the members table
just the first 4 characters of the postcode match?!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-22 : 14:20:04

seems to be an Access question, not SQL Server. I know little about access so I can't help you.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources