I wanted a query that shows just how far there is to go, chasing DX.
But for anyone to use this there are some prerequisites.
For the impatient, I put the whole kit of script/SQL on a repository.
Clone this and modify to suit.
First, you are using Logging Software that stores your QSO's in a SQL database (MySQL, etc. -- as long as it can comprehend SQL statements)
Second, when your Logging Software stores each QSO, there is a field in that record called country
and a field called callsign. The country field is the name of the DX entity, not the ID number of the entity. So for example, for United States, the value of country is 'United States' not 291.
The callsign field is self explanatory.
Next, you'll need another table. I call it dxcc. I had to make it.
It is a table of all of the DXCC entities.
It has this schema:
mysql> desc dxcc;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | YES | | NULL | |
| cqz | int | YES | | NULL | |
| ituz | int | YES | | NULL | |
| continent | varchar(4) | YES | | NULL | |
| pfx | varchar(16) | YES | | NULL | |
| dxid | int | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
So for instance, the table dxcc contains records like this:
mysql> select * from dxcc limit 5;
+------+-------------------------+------+------+-----------+------+------+
| id | name | cqz | ituz | continent | pfx | dxid |
+------+-------------------------+------+------+-----------+------+------+
| 1039 | Sov Mil Order of Malta | 15 | 28 | EU | 1A | 246 |
| 1040 | Spratly Is. | 26 | 50 | AS | 1S | 247 |
| 1041 | Monaco | 14 | 27 | EU | 3A | 260 |
| 1042 | Agalega & Saint Brandon | 39 | 53 | AF | 3B6 | 4 |
| 1043 | Mauritius | 39 | 53 | AF | 3B8 | 165 |
+------+-------------------------+------+------+-----------+------+------+
The critical things are that each entity has a name (stored in the field name) and what is important is that your DXCC table that stores names of entities uses the same naming convention as your logging software. I won't go into how to reconcile that, but that's a requirement also. The easiest way to generate that list though is either to snag the ARRL DXCC List or to use your Logging Software and poke around to find where they have the data for "all known DXCC entities" (not just worked DX but all known DX as per your software).
The dxid field is just the ARRL assigned DX entity ID number, for future reference.
Anyway.
With that in hand you can run this query:
SELECT d.name AS dxcc_entity,
COUNT(l.callsign) AS callsign_count
FROM dxcc d
LEFT JOIN log l ON l.country = d.name
GROUP BY d.name ORDER by 1
It's going to count up the number of records (if any) that are produced by an outer-join between the dxcc table (the table of all DX entities) and the table Log (which is the name I use for the Logging Software).
When for each DXCC entity (by name) there are any records in Log (stations I've worked) that share the same name, it adds to the total for that DXCC entity. The GROUP BY establishes that this aggregate (count) is done per country in table dxcc table.
What's the result? It tells me how many contacts per country. What's next is to modify the query to tell me number of contacts per country per band.
For those who do their own software, logging tools, etc.. It might be useful to you.
Update: For the 5BDXCC query, there's a need for a table that aggregates the totals per DXCC entity. SQL (doesn't matter what the RDBMS is) is not quite suited for what people call "Pivot Tables" in Excel. It's possible, but it's more tedious to work out the query. Instead I created another table to perform the function of the pivot:
mysql> desc sum;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(150) | YES | | NULL | |
| cqz | int | YES | | NULL | |
| ituz | int | YES | | NULL | |
| continent | varchar(4) | YES | | NULL | |
| pfx | varchar(16) | YES | | NULL | |
| dxid | int | YES | | NULL | |
| m160 | int | YES | | NULL | |
| m80 | int | YES | | NULL | |
| m40 | int | YES | | NULL | |
| m30 | int | YES | | NULL | |
| m20 | int | YES | | NULL | |
| m17 | int | YES | | NULL | |
| m15 | int | YES | | NULL | |
| m12 | int | YES | | NULL | |
| m10 | int | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
First thing to do is fill in the sum table with the names and attributes of the dxcc table.
We will get to the totals (m80, m40, m30 etc..) later
INSERT into sum
(name, cqz, ituz, continent, pfx, dxid)
SELECT d.name, d.cqz, d.ituz, d.continent, d.pfx, d.dxid
FROM dxcc d;
After that, per each field m160, m80, m40 and so on this query needs to be run, but with a caveat.
You'll need to instrument some embedded SQL to iterate through all DXCC ID's (they are stored in the field DXID. So for instance the lower-48 is 291, etc.)
So, wrap this stanza in a loop that iterates over all of the DXID where each DXID is N.
UPDATE sum s
SET s.m20 = (
SELECT COUNT(l.callsign)
FROM dxcc d
LEFT JOIN log l
ON l.country = d.name
AND l.band = '20m'
GROUP BY d.pfx, d.dxid having d.dxid = N)
WHERE s.dxid = N
You cannot use this literally. It's meant for embedded SQL. Your embedded SQL wrapper needs to specify the values for N. They are integers. I could show you how I wrap it, but I'd rather just email it to you than further cloud up this page. If you're really interested, just ping me.
But that's not all. That just sets the "20 meter" total per DXID. If we want to do all of the bands, we modify the statement above to look like this instead. This is one statement. We are SETting on several fields per the condition in each sub-query finally "WHERE s.dxid = N". I used some perl code to generate the SQL statements and then shove them into the RDBMS. If you really want to run with this, and need help just ping me.
UPDATE sum s
SET
s.m20 = (
SELECT COUNT(l.callsign)
FROM dxcc d
LEFT JOIN log l
ON l.country = d.name
AND l.band = '20m'
GROUP BY d.pfx, d.dxid having d.dxid = N), /* note comma */
s.m40 =
SELECT COUNT(l.callsign)
FROM dxcc d
LEFT JOIN log l
ON l.country = d.name
AND l.band = '40m'
GROUP BY d.pfx, d.dxid having d.dxid = N), /* note comma */
/* and so on, for each band. Cannot name fields '80m', so the
field is 'm80'. I didn't bother creating the records for 160m
or 6m since I don't work a lot of DX on those bands.
*/
WHERE s.dxid = N;
/* That is the last line of the one statement. It's a long SQL statement to update all of the per-band fields. You don't have to do it in one statement, but it's just easier for me. */
Finally, when all is loaded into the sum table, I can generate the HTML for the "Wanted List"
This page is generated via more software (a simple perl script that is based on the data in the sum table above.
A sample. I chose to use grey background not traditional "red" because for me there would be too much red (hi hi).
