Do you have a question? Post it now! No Registration Necessary. Now with pictures!
September 4, 2005, 5:43 pm
rate this thread
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.
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.
Re: Help with an SQL query?
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
SELECT Fld2 AS TickerSymbol FROM aTable WHERE Fld1 IS NOT NULL
SELECT Fld3 AS TickerSymbol FROM aTable WHERE Fld1 IS NOT NULL)
GROUP BY TickerSymbol;
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum