List:General Discussion« Previous MessageNext Message »
From:Richard Dice Date:October 27 1999 1:10pm
Subject:Question re: "group by" in a query
View as plain text  
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
Thread
Question re: "group by" in a queryRichard Dice27 Oct
  • Re: Question re: "group by" in a queryBob Kline27 Oct
  • Re: Question re: "group by" in a querysinisa27 Oct