SQL hints

Any useful tips you can offer fellow users can be posted here.

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

(sorry I first posted this under help and I should have put it here):

I have found another SQL that works well for searching the log for hams that I have had over 'x' qsos with over the years! Neat as I have been trying to find that out for awhile.

This works: (30 qsos in this example)

SELECT callsign,COUNT(*) FROM log
GROUP BY callsign HAVING COUNT(*)>30
N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

Greetings WinLog32 users,

I continue to play with some SQL and thought I would share the results for those interested. Check for date formats but other than that I think the following work fine: (Some of these may be handled by the program itself instead of a search but if you use the search and need SQL this works)

** To search for how many unique callsigns in your log:
SELECT DISTINCT callsign FROM LOG

SELECT DISTINCT CALLSIGN FROM LOG AND WHERE CODE NOT LIKE 291

SELECT DISTINCT CALLSIGN FROM LOG AND WHERE MODE LIKE 'CW'

Note: I imagine you can do some other variations to the above (code 291 is USA - change to meet your needs - same with mode of course.)

** The next examples deal with dates:

To search for all entries between two dates:
SELECT * FROM LOG WHERE date between #1/1/12# and #12/31/12#

To search between dates and add a mode:
SELECT * FROM LOG WHERE date between #1/1/12# and #12/31/12# and mode like 'cw'

To search for DXCC excluding USA after a certain date (or < for before):
SELECT * FROM LOG WHERE Date > #12/31/12# AND code not LIKE 291

To search for how many dxcc starting a certain date:
SELECT DISTINCT code FROM LOG and where date > #12/31/12#

To search for how many dxcc on a particular band prior to a specifc date:
SELECT DISTINCT code FROM LOG and WHERE Date < #3/9/13# and band like "24*"

Note: Again, you can see the above to get you started but it gives you the idea.

** The next are examples of searching for specific counts:

To search callsigns worked over 'x' times (30 in this example):
SELECT callsign,COUNT(*) FROM log GROUP BY callsign HAVING COUNT(*)>30

To search for frequency multiple occurances (200 in this example):
SELECT band,COUNT(*) FROM log GROUP BY band HAVING COUNT(*)> 200

To search for number of qso in a particular band:
SELECT * FROM LOG WHERE Band LIKE '18*'

To search for QRP 1-5 watts power:
SELECT * FROM LOG WHERE Pwr LIKE '[1-5]'

To search for how many dxcc worked in QRP:
SELECT DISTINCT code FROM LOG and where pwr like '[1-5]'

To search for a specific DXCC number of qsos (Japan in this example):
SELECT * FROM LOG WHERE code LIKE 339

Hope you enjoy the above and I am a rookie with SQL for sure but having fun. Maybe others will share some neat SQL they have used.

Gary Stone, N5PHT
N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 

SQL hints

Postby G0CUZ » Tue Mar 05, 2013 5:22 am

Thanks for sharing the SQL Gary.

You may also be interested experimenting with the "Search - Advanced" feature, like the name suggests this is more for the advanced user.

This uses 'plug-in' files which contain ready made sets of SQL queries and the files and contents are editable and expandable resulting in a permanent set of queries.

The editor may take a little learning but, with your new found SQL knowledge I'm sure you'll have a lot of fun, and the resulting 'plug' files can saved and be shared.

The advantage is that the queries are saveable and can be run on any of the databases in the Winlog32 collection.

Colin, G0CUZ
G0CUZ
Site Admin
 
Posts: 1628
Joined: Wed Jan 02, 2002 7:12 am
 

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

Colin,

Thanks and yes I have played with the plug-ins feature. However, I often do searches to look for "how many..." When I have tried the plug-in feature I can't seem to see the totals anyplace? For example, in the case I am not explaining very well: When I do a search in the search box and use SQL the search window in the main program has the results including the # in the top of the pane. But when I use the plug-in another box opens that seems to be different from the search box and I don't see a total. Like doing an SQL for how many 339 (Japan) will tell me but if I do it in the plug-in I see the contacts for Japan listed but not the total number? Am I missing something?

Gary, N5PHT

quote:
Originally posted by G0CUZ

Thanks for sharing the SQL Gary.

You may also be interested experimenting with the "Search - Advanced" feature, like the name suggests this is more for the advanced user.

This uses 'plug-in' files which contain ready made sets of SQL queries and the files and contents are editable and expandable resulting in a permanent set of queries.

The editor may take a little learning but, with your new found SQL knowledge I'm sure you'll have a lot of fun, and the resulting 'plug' files can saved and be shared.

The advantage is that the queries are saveable and can be run on any of the databases in the Winlog32 collection.

Colin, G0CUZ

N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 

SQL hints

Postby G0CUZ » Tue Mar 05, 2013 5:22 am

Hi Gary

Good point about the editor and counting the records, as you say - it doesn't seem to give a total and this would be useful.

I will investigate and think it should be easy to add a count as it is usually only a record count so will fix this for the next version.

73 Colin
G0CUZ
G0CUZ
Site Admin
 
Posts: 1628
Joined: Wed Jan 02, 2002 7:12 am
 

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

Colin - when that happens I will be happy on several and share them.

Gary, N5PHT

quote:
Originally posted by G0CUZ

Hi Gary

Good point about the editor and counting the records, as you say - it doesn't seem to give a total and this would be useful.

I will investigate and think it should be easy to add a count as it is usually only a record count so will fix this for the next version.

73 Colin
G0CUZ

N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

Another SQL goodie: (help from Andrew Cumming at http://sqlzoo.net):

To get a count for how many qso per day over a range - or for that matter it will tell you how many days of the past whatever range you have at least one qso. (I know some CW folks trying for at least a cw contact per day, etc.)

SELECT Date, COUNT(1)
FROM LOG
WHERE Date BETWEEN #3/1/12# AND #3/12/12# AND mode like 'cw'
GROUP BY Date

** Remember to make the date as your log is format and for no mode just drop the AND mode like 'cw'

I used the above (with different dates) to see how many days I operated at least one day since being licensed in 1986 - cool!!

Gary, N5PHT
N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 

SQL hints

Postby N5PHT » Tue Mar 05, 2013 5:22 am

Another SQL (again, with assistance from Andrew at sqlzoo.net)

This is a neat search if you are interested to know how many qsos you have made per month over any period of time. I used it to check how many qsos I have made each month since being licensed in 1986. The result is a list by year and month. Of course you can drop the mode line or another mode, etc. And of course put in whatever dates you desire. (This prompted me to try and break my record of 1008 qso in a month - sitting at about 815 now and still have several days left so maybe!)

select year(date), month(date), count(*)
from log
WHERE Date BETWEEN #1/1/86# AND #2/28/13# AND mode like 'cw'
group by year(date), month(date)


Gary, N5PHT
N5PHT
Keen user
 
Posts: 37
Joined: Thu Feb 28, 2013 7:06 am
 
 

Return to Hints and Tips