Posted by Stefan Rybacki on 09/05/05 01:19

Martin wrote:
> 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.

- Try something like this (untestet but should work on mysql 4.1 and above)

SELECT t,count(t) FROM (
SELECT fld1 as t FROM table WHERE NOT (fld1 IS NULL)
UNION
SELECT fld2 as t FROM table WHERE NOT (fld2 IS NULL)
UNION
SELECT fld3 as t FROM table WHERE NOT (fld3 IS NULL)
) x GROUP BY t


- BUT better *repair* your database design

Stefan

>
> Help?
>

[Back to original message]