MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brian Neal Date:June 20 1999 3:25am
Subject:Re[2]: Help with select query
View as plain text  
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 |         |       |
+------------+------------------+------+-----+---------+-------+

No, it's not specifically for SETI@Home, though it is general enough that it
works fine with it, and I have imported and generated reports from SETI
statistics.  It's a statistics database for common distributed projects over
the internet.  Currently, I am rendering statistics for RC5 using
distributed.net's client/server software (for which I have written a program to
interface with my database).  It's also possible to do the same thing with
SETI.

I designed it because I felt that most of the projects, with distributed.net in
particular, were having quite a bit of trouble.  So, myself and a few others
designed a spec for a new distributed system.  This database is one part of
that spec, and I simply convert the existing distributed.net data.  It's been
running fairly reliably for some time, but I just upgraded my server, which has
given me more CPU time to tackle some more complicated queries, if I can ever
figure them out. :P

Thanks for the help,

-Brian

Garrick Staples wrote:

> You might want to send your table descriptions; this one's a bit tricky.
>
> But I offer the addition of MAX() around SUM() and email_addr to the
> GROUP clause:
> SELECT cpu_types.cpu_name,MAX(SUM(user_stats.block_size))
> AS cpu_totals, user_stats.email_addr
> FROM user_stats
> CROSS JOIN cpu_types
> WHERE user_stats.cpu_type=cpu_types.cpu_number
> GROUP BY user_stats.email_addr,cpu_types.cpu_name ORDER BY cpu_totals
> DESC;
>
> This sounds like something for the SETI@home project??
>
> Brian Neal wrote:
> >
> > Hello, now that my database is operational again, I've been working out
> > some new queries, but for some reason I just can't hack this one.
> >
> > I'm trying to return a list of users and their predominate architecture
> > (which is determined simply by how often it shows up in the table).  The
> > architecture name is obtained through a CROSS JOIN with another table
> > that references a number code with a plain english name (the number is
> > in the primary table, user_stats).
> >
> > SELECT cpu_types.cpu_name,SUM(user_stats.block_size)
> > AS cpu_totals
> > FROM user_stats
> > CROSS JOIN cpu_types
> > WHERE user_stats.cpu_type=cpu_types.cpu_number
> > GROUP BY cpu_types.cpu_name ORDER BY cpu_totals DESC;
> >
> > +----------+------------+
> > | cpu_name | cpu_totals |
> > +----------+------------+
> > | x86      |    1775725 |
> > | SPARC    |     319571 |
> > | PowerPC  |     109199 |
> > | Alpha    |      78151 |
> > | PA-RISC  |      15292 |
> > | MIPS     |       2361 |
> > +----------+------------+
> >
> > This query will return the total usage, grouped by CPU architecture.  In
> > my table structure, I have a field for an email_address, which is the
> > identifier I'd like to use.
> >
> > SELECT cpu_types.cpu_name,SUM(user_stats.block_size)
> > AS cpu_totals
> > FROM user_stats
> > CROSS JOIN cpu_types
> > WHERE user_stats.cpu_type=cpu_types.cpu_number
> > AND user_stats.email_addr='my@stripped'
> > GROUP BY cpu_types.cpu_name ORDER BY cpu_totals DESC;
> >
> > +----------+------------+
> > | cpu_name | cpu_totals |
> > +----------+------------+
> > | Alpha    |      69989 |
> > | x86      |      24566 |
> > | SPARC    |        146 |
> > +----------+------------+
> >
> > This query uses the email address in the WHERE clause to pull up the CPU
> > totals for an individual user.  This is closer, but what I want is a
> > list of all the email addresses with their top CPU type.
> >
> > SELECT cpu_types.cpu_name,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 cpu_types.cpu_name ORDER BY cpu_totals DESC;
> >
> > So, I give this a whirl, but of course it doesn't work (that's
> > obvious).  Unfortunately, I can't seem to come up with the solution.
> > Anyone have any clues?
> >
> > -Brian
> >
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > posting. To request this thread, e-mail mysql-thread5508@stripped
> >
> > To unsubscribe, send a message to the address shown in the
> > List-Unsubscribe header of this message. If you cannot see it,
> > e-mail mysql-unsubscribe@stripped instead.

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