MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brian Neal Date:June 20 1999 10:11pm
Subject:Re[3]: Help with select query
View as plain text  
Well, I figured I'd have to end up using a temporary table....and this works great
(I was looking for a SELECT INTO, but couldn't find it):

  INSERT INTO tmp_table SELECT email_addr, os_name, SUM(block_size)
  AS cnt
  FROM user_stats, os_types
  WHERE os_type = os_number
  GROUP BY email_addr, os_type ORDER BY email_addr, cnt;

So, the temp table is created, but unfortunately, I'm not having much luck with
MAX():

  SELECT email_addr, os_name, block_size
  AS cnt
  FROM tmp_table
  WHERE MAX(cnt);
  ERROR 1111: Invalid use of group function

Hmmm....I tried a couple different variations but I still can't get it to work.
If only this was Oracle. :P  Anyway, the version of MySQL I'm running is 3.22.23b,
if that helps.

Thanks again,

-Brian

Jim Faucette wrote:

> Brian Neal wrote:
> >
> > Well, this seemed like a good plan, but adding MAX() around SUM() has so far
> > only given me this error message:
> >
> >   ERROR 1111: Invalid use of group function
> >
> > Once again, this is the modified query (with MAX()):
> >
> >   SELECT cpu_types.cpu_name,MAX(SUM(user_stats.block_size)),email_addr
> >   AS cpu_totals
> >   FROM user_stats
> >   CROSS JOIN cpu_types
> >   WHERE user_stats.cpu_type=cpu_types.cpu_number
> >   GROUP BY user_stats.email_addr ORDER BY cpu_totals DESC;
> >
> > Here's the primary table, user_stats:
> > +-------------+------------------+------+-----+------------+-------+
> > | Field       | Type             | Null | Key | Default    | Extra |
> > +-------------+------------------+------+-----+------------+-------+
> > | submit_date | date             |      | MUL | 0000-00-00 |       |
> > | submit_time | time             | YES  |     | NULL       |       |
> > | ip_addr     | char(15)         | YES  |     | NULL       |       |
> > | email_addr  | char(128)        |      | MUL |            |       |
> > | block       | char(16)         |      | PRI |            |       |
> > | block_size  | int(10) unsigned | YES  |     | NULL       |       |
> > | os_type     | int(10) unsigned |      |     | 0          |       |
> > | cpu_type    | int(10) unsigned |      |     | 0          |       |
> > | client_ver  | int(10) unsigned | YES  |     | NULL       |       |
> > +-------------+------------------+------+-----+------------+-------+
> >
> > os_types:
> > +-----------+------------------+------+-----+---------+-------+
> > | Field     | Type             | Null | Key | Default | Extra |
> > +-----------+------------------+------+-----+---------+-------+
> > | os_number | int(10) unsigned |      | PRI | 0       |       |
> > | os_name   | char(30)         |      | MUL |         |       |
> > +-----------+------------------+------+-----+---------+-------+
> >
> > cpu_types:
> > +------------+------------------+------+-----+---------+-------+
> > | Field      | Type             | Null | Key | Default | Extra |
> > +------------+------------------+------+-----+---------+-------+
> > | cpu_number | int(10) unsigned |      | PRI | 0       |       |
> > | cpu_name   | char(16)         |      | MUL |         |       |
> > +------------+------------------+------+-----+---------+-------+
> >
>
> To find the os usage:
> SELECT os_name, COUNT(os_type) AS cnt
> FROM user_stats, os_types
> WHERE os_type = os_number
> GROUP BY os_type ORDER BY cnt;
>
> For a person:
> SELECT email_addr, os_name, COUNT(os_type) AS cnt
> FROM user_stats, os_types
> WHERE email_addr = 'jimf@stripped' AND os_type = os_number
> GROUP BY os_type ORDER BY cnt;
>
> For all users:
> SELECT email_addr, os_name, COUNT(os_type) AS cnt
> FROM user_stats, os_types
> WHERE os_type = os_number
> GROUP BY email_addr, os_type ORDER BY email_addr, cnt;
>
> The simplest way to get the top OS for each user would be to create a
> temp table with: email_addr, os_name, cnt.
>
> INSERT INTO tmp_table SELECT ... (for all users)
> SELECT * FROM tmp_table WHERE max(cnt) GROUP BY email_addr;
> Then delete the temp table.
>
> I haven't tried this, but it may do want.
>
>   jim...

Thread
Help with select queryBrian Neal20 Jun
  • Re[2]: Help with select queryBrian Neal20 Jun
  • Re[3]: Help with select queryBrian Neal21 Jun
    • Re[3]: Help with select queryMichael Widenius29 Jun