Counting Distinct Columns

[mattyvx]

SQL Server version: 5.0.85

 

Table Name: Instructors

---------------------------------------

Name  | City          |    City1

John    Liverpool      Manchester

Ian      Manchester 

Paul    London       

---------------------------------------

Hi,

 

As shown above I have a table where members are registered and they can choose up two towns to be assigned to. Paying members can be assigned to two towns whilst free members can only be assigned to one town.

 

What i want to do is count how many members are displayed in each town and then order this by the most popular towns. For example the result of the above should be:

 

City              | Count

Manchester    2

Liverpool        1

London          1

 

Currently i am using

 

SELECT City, COUNT(City) as count
FROM Instructors
GROUP BY City
ORDER by count DESC
LIMIT 5

 

 

Which works great if there are no paying members as it will count the City column. However now i have paying members I need the count to include the City1 column also.

 

Any thoughts?

[Mchl]

Start with normalising your database:

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[kickstart]

Hi

 

Agree with the above, but if you can't:-

 

SELECT City, COUNT(City) as count
FROM (SELECT City
FROM Instructors
UNION ALL
SELECT City1 AS City
FROM Instructors)
GROUP BY City
ORDER by count DESC

 

All the best

 

Keith

[mattyvx]

Thanks for your suggestions.

 

So after reading the article am I on the right lines with taking the City field out of the table.

 

I would have a many to many relationship between ID and City. Many ID's can be registered to many cities?

 

But then i have no primary key in that table...

 

Can I have a table like this with two fields and no primary key? or would i need to create another column as a primary key?

 

P.S tried the SQL and got an mysql_fetch_array() error.

[Mchl]

Good thinking.

For many-to-many table you can create a primary key consisting of both columns.

[mattyvx]

Ok,

 

Now I have something like this

 

Listing_ID        ID      City

1Manchester    1        Manchester

1Liverpool        1        Liverpool

2Manchester    2        Manchester

3London          3        London

 

I want to Count how many 'ID's are in each area and have the results display like.

 

Manchester  2

Liverpool      1

London        1

 

I'm more of a PHP/HTML'er so as you can guess im still learning SQL...learning and willing to learn

[kickstart]

Hi

 

If you want a listing Id unique field then just make it an int set to autonumber.

 

However you can do a count with:-

 

SELECT City, COUNT(ID)
FROM someTableName
GROUP BY City

 

All the best

 

Keith

[Mchl]

Lol. When I said 'you can create a primary key consisting of both columns' I meant something like this:

 

ALTER TABLE user_to_city ADD PRIMARY KEY (userID,cityID)

(assuming column are called userID and cityID)

[mattyvx]

....Maybe i will make an autonumber.

 

Thanks for your help, its working great.

 

Though now ive changed the table setup I have a few other queries which need changing...

 

The variable $city is defined.

 

The following query selects member details from a certain area.

 

SELECT ID,Name,Price,Website,Contact,Profile FROM Members WHERE City='$city'

 

City was in "Members" and now it is in the table we have created above "Cities".

 

I take it I need to Join a query between the two tables?

[Mchl]

Yeah.

Optimally you should have three tables. One for Members, one for Cities, and one that connects both (and has only two columns with ids).

 

Then it's like this.

 

SELECT
m.ID,m.Name,m.Price,m.Website,m.Contact,m.Profile 
FROM Members AS m
INNER JOIN Members_to_Cities AS m2c
  ON m.ID = m2c.memberID
INNER JOIN Cities AS c
  ON c.ID = m2c.cityID
WHERE
  c.City = '$city';