Posted by Martin on 09/05/05 03:04

Stefan:

Thanks. The only thing I had to debug was to change UNION to UNION
ALL. UNION returns only unique values; UNION ALL returns all values.

Now, for my education, why is the "x" just ahead of GROUP BY
necessary? If I remove it, no records are returned, so it's obviously
needed. But I don't understand what it actuall IS.

Martin


On Mon, 05 Sep 2005 00:19:19 +0200, Stefan Rybacki
<stefan.rybacki@gmx.net> wrote:

>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]