Help with an SQL query?

[Martin]

I have a table in which the records a bunch of fields; for this query,
I'm interested in three of them - let's call them Fld1, Fld2 and Fld3.
For each record in the table, these fields may or may not contain data
(a stock ticker symbol - like MSFT, for example). Different records
may or may not contain the same symbol in the various fields.

For example:
Fld1 Fld2 Fld3
Record1: MSFT | GOOG | null
Record2: INTL | ABC | MSFT

I want to execute a query that will read all of the records in the
table and return a single-column list of all the unique symbols and a
count of the quantity of each one - like so:

MSFT | 2
GOOG | 1
INTL | 1
ABC | 1

I have no idea how to do this.

Help?

[Bill Karwin]

Martin wrote:

For example:
Fld1 Fld2 Fld3
Record1: MSFT | GOOG | null
Record2: INTL | ABC | MSFT

I want to execute a query that will read all of the records in the
table and return a single-column list of all the unique symbols and a
count of the quantity of each one - like so:

MSFT | 2
GOOG | 1
INTL | 1
ABC | 1

It would much easier, more scaleable, and better relational design, to
separate Fld1/Fld2/Fld3/...FldN (I am assuming you have more than three
Fld's and are simplifying for purposes of the example problem) and make
them a single field in a separate table, referencing your primary table.
Then you could just use:
SELECT TickerSymbol, COUNT(*) FROM Tickers GROUP BY TickerSymbol

For this example, however, you could use a derived table in a subquery
if you use MySQL 4.1 or later:

SELECT TickerSymbol, COUNT(*)
FROM (SELECT Fld1 AS TickerSymbol FROM aTable WHERE Fld1 IS NOT NULL
UNION ALL
SELECT Fld2 AS TickerSymbol FROM aTable WHERE Fld1 IS NOT NULL
UNION ALL
SELECT Fld3 AS TickerSymbol FROM aTable WHERE Fld1 IS NOT NULL)
AS unionTable
GROUP BY TickerSymbol;

Regards,
Bill K.