PHP SQL - DISTINCT and count(*)

Author

DISTINCT and count(*)

Andreas

2005-05-16, 3:57 am

Hi Guys,

I am having difficulties building an SQL statement for my particular
scenario. I was hoping one of you could help me:

I have got a table (user) with a range of registered users. Each of them
entered their postcode when they registered.
Then I have got a table (suburbs) with all my country's suburbs, postcodes
and states in it.

Now I need an SQL statement that will tell me how many users there are per
state. In theory that would be simple, but unfortunately some of my
postcodes will appear multiple times in my suburbs table (two suburbs can
have the same postcode but different names). Because of my postcodes showing
up multiple times in the suburbs table, all my numbers are incorrect.

Here's the SQL I tried:

SELECT suburbs.state, count(suburbs.state) as No FROM user, suburbs WHERE
user.postcode=suburbs.postcode GROUP BY suburbs.state

So if I have got a single user registered with the postcode 3044, and this
particular postcode shows up twice in the suburbs table, my result will be:

State: VIC
No: 2

But obviously I would like the result to be:

State: VIC
No: 1

I am sure this must be fairly simple to fix, but I just can't come up with
the right solution. I was thinking that a DISTINCT might help, but it
doesn't really in combination with count(*).

Any ideas?

2005-05-16, 3:57 pm

Andreas wrote:
>
> I am having difficulties building an SQL statement for my particular
> scenario. I was hoping one of you could help me:
>
> I have got a table (user) with a range of registered users. Each of
them
> entered their postcode when they registered.
> Then I have got a table (suburbs) with all my country's suburbs,
postcodes
> and states in it.
>
> Now I need an SQL statement that will tell me how many users there
are per
> state.

Try this:

SELECT suburbs.state AS state, COUNT(user.postcode) AS users
FROM user LEFT JOIN suburbs ON user.postcode=suburbs.postcode
GROUP BY suburbs.state

Cheers,
NC

Sponsored Links