Page 1 of 1

Counting countries once in DB...help?

Posted: Sun Apr 08, 2007 6:24 pm
by aymie
I'm trying to union all my tables and group by countries using something like this:

$query = "SELECT DISTINCT(`country`) FROM (SELECT COUNT(DISTINCT(`country`) FROM $table UNION ALL) AS `total` GROUP BY `country`";

Because I have 25 member tables in my database and I keep adding tables (each table is a different subject) and 951 members in total from 220 countries (including repeated countries) BUT it's ACTUALLY 60 different countries without repeats (I went through and counted manually).

HOWEVER, mysql is counting repeated countries e.g. USA 30 times when I only want the repeating countries to be counted ONCE.

How do I get the countries to be counted only once? The query example above is giving me 0 and when I write it any other way it gives me 220.
I came to that code from reading MANY tutorials of how to select something within a select and unioning all tables to return a result, but it's not doing what I want it to do.

Can someone please help me?

Posted: Sun Apr 08, 2007 6:30 pm
by feyd
Is the user associated with a country?

Posted: Sun Apr 08, 2007 7:01 pm
by aymie
Yup it's like this in my table:

music fan table:
id name email country website approved added

song fan table:
id name email country website approved added

and so on for different tables.

The problem is, different users are from different countries and not always appearing in the all the tables e.g.
name: mary
country: canada
(shown in all tables)

name: jack
country: usa
(not shown in all tables)

So I just want to count the countries e.g usa, canada etc. only ONCE even if they appear 20 times over all my tables...is this doable?

Posted: Sun Apr 08, 2007 7:20 pm
by feyd
Why not have the users all in a single table and the "fan" tables associate the user id and approved statuses?

Posted: Sun Apr 08, 2007 7:35 pm
by aymie
because I have over 900 users spread across 25 tables (and growing). I didn't write the script but I did want to echo the counted country stats on my site.

I can't move my users to their own little table e.g. users and countries to its own little table e.g. countries or else it'd break my script AND it means whenever someone signs up I'd have to manually insert them into users and countries.

Posted: Mon Apr 09, 2007 7:12 am
by Begby
This table structure is a recipe for disaster and countless future problems, you really should consider starting over....

As for the query you posted you are selecting a count, then from that result set selecting 'country' which doesn't exist in your subquery result set.

I think you want something like SELECT COUNT(country) (SELECT DISTINCT country.......