Counting countries once in DB...help?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aymie
Forum Newbie
Posts: 3
Joined: Sun Apr 08, 2007 6:22 pm

Counting countries once in DB...help?

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is the user associated with a country?
aymie
Forum Newbie
Posts: 3
Joined: Sun Apr 08, 2007 6:22 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why not have the users all in a single table and the "fan" tables associate the user id and approved statuses?
aymie
Forum Newbie
Posts: 3
Joined: Sun Apr 08, 2007 6:22 pm

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.......
Post Reply