Hello, all...
I've got a quick question re: an aspect of SQL on MySQL that's vexxed me
for quite a while now. I'm sure that the problem is one of misunderstanding
on my part, either re: how MySQL operates, or how SQL is supposed to handle
the following type of situation.
The situation is that I have a table with a varchar column, and I want to
get a report containing the leftmost 2 characters of each column, with
a count of the number of times that 2-char string occurs. IE.
if I had in a table containing:
foo
bar
baz
quux
Then I'd want a report that looks like:
fo 1
ba 2
qu 1
Here's what I've tried:
=========================================================================
mysql> describe sic_desc_tb;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| sic_code | int(11) | | PRI | 0 | |
| description | varchar(50) | | | | |
| is_venue | tinyint(4) | | | 0 | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select left(description, 2), count(*) from sic_desc_tb group by
> left(description, 2);
ERROR 1064: You have an error in your SQL syntax near 'left(description, 2)' at line 1
=========================================================================
Obviously, it can't handle "group by FUNCTION". "group by COLNAME" works
fine, but it doesn't produce the report that's helpful to me.
Suggestions for how to do this in a single MySQL SQL command?
(Or do I have to use temp tables, or handle it programmatically in Perl,
etc.)
Cheers,
Richard
----------------------------------------------------------------------------
Richard Dice * Personal 514 816 9568 * Fax 514 816 9569
Director of Software Development, HBE Interactive Inc. * http://www.hbe.ca/
ShadNet Creator * http://shadnet.shad.ca/ * rdice@stripped
Occasional Writer, HotWired * http://www.hotwired.com/webmonkey/
"squeeze the world 'til it's small enough to join us heel to toe"
- jesus jones
