MySQL Lists are EOL. Please join:

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

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