#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    21
    Rep Power
    0

    Need "Top 5" Query Help...


    Hello everyone,
    I am having problem with a query. I need to get:

    produce a list of the five members of roster_cs368
    and their ids who use the most space (number of bytes)
    in htmp_cs368 and atmp_cs368 in descending order--
    greediest first.

    Here is the query I have come up with but it does not seem to be working. I only get one student id, name, bytes, etc. and then NULL.

    Here is the query:
    Code:
    (
      SELECT id,
             firstName,
             lastName,
             SUM(fileSize) AS TotalBytes,
             SUM(fileSize)/COUNT(*) AS Average
      FROM   roster_cs368 AS a
        JOIN htmp_cs368  AS b USING (id)
    ) UNION (
      SELECT id,
             firstName,
             lastName,
             SUM(fileSize) AS TotalBytes,
             SUM(fileSize)/COUNT(*) AS Average
      FROM   roster_cs368 AS a
        JOIN atmp_cs368  AS b USING (id)
    )
    ORDER BY TotalBytes DESC
    LIMIT 5
    Can someonee help me please? I would really appreciate it.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT id
                  , firstName
                  , lastName
                  , SUM(fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             UNION ALL
             SELECT id
                  , firstName
                  , lastName
                  , SUM(fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             ) AS u
    ORDER 
        BY TotalBytes DESC LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    21
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT id
                  , firstName
                  , lastName
                  , SUM(fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             UNION ALL
             SELECT id
                  , firstName
                  , lastName
                  , SUM(fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             ) AS u
    ORDER 
        BY TotalBytes DESC LIMIT 5
    I appreciate your help but I get an error stating:

    Code:
    ERROR 1052 (23000): Column 'id' in field list is ambiguous
    My schema is as follows:

    Code:
    mysql> select * from roster_cs368
        -> ;
    +--------+-----------+-----------+
    | id     | firstName | lastName  |
    +--------+-----------+-----------+
    | apn7cf | Allen     | Newton    |
    | atggg3 | andrew    | goebel    |
    Where id is the primary key,

    Code:
    mysql> select * from htmp_cs368;
    +------------+----------+------------+----------+----------+-------+------+-------+----------------------+
    | filePerms  | numLinks | id         | idGroup  | fileSize | month | day  | time  | fileName             |
    +------------+----------+------------+----------+----------+-------+------+-------+----------------------+
    | drwx------ |        2 | schulte    | faculty  |      289 | Nov   |    7 | 2011  | Java                 |
    | -rw-r--r-- |        1 | schulte    | faculty  |      136 | Apr   |   29 | 2012  | LD                   |
    | drwxr-xr-x |        3 | schulte    | faculty  |      177 | Mar   |   20 | 2012  | Upgrade              |
    No primary key here,

    Code:
    mysql> select * from atmp_cs368;
    +------------+----------+--------------+----------+----------+-------+------+-------+-----------------------------+
    | filePerms  | numLinks | id           | idGroup  | fileSize | month | day  | time  | fileName                    |
    +------------+----------+--------------+----------+----------+-------+------+-------+-----------------------------+
    | drwxr-xr-x |        2 | remierm      | 203      |      245 | Sep   |   17 | 14:40 | 148360_sun_studio_12        |
    | drwx---rwx |       31 | antognolij   | sasl     |     2315 | Oct   |   24 | 12:28 | 275                         |
    | -rwx------ |        1 | kyzvdb       | student  |       36 | Sep   |   19 | 13:05 | 275hh                       |
    and no primary key here.

    Of course the table have much more information in them. This is just a snippet.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by csharp100
    I appreciate your help but I get an error ...
    of course, yes, sorry...

    ... but you would get that error on the query you posted, too
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             UNION ALL
             SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             ) AS u
    ORDER 
        BY TotalBytes DESC LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    21
    Rep Power
    0
    Originally Posted by r937
    of course, yes, sorry...

    ... but you would get that error on the query you posted, too
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             UNION ALL
             SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             ) AS u
    ORDER 
        BY TotalBytes DESC LIMIT 5
    So close yet so far, here is my screenshot;
    Code:
    mysql> SELECT id
        ->      , firstName
        ->      , lastName
        ->      , SUM(SubTotalBytes) AS TotalBytes
        ->      , SUM(SubTotalBytes) /
        ->             SUM(SubCount) AS TotalAverage
        ->   FROM ( SELECT a.id
        ->               , a.firstName
        ->               , a.lastName
        ->               , SUM(b.fileSize) AS SubTotalBytes
        ->               , COUNT(*)      AS SubCount
        ->            FROM roster_cs368 AS a
        ->          INNER
        ->            JOIN htmp_cs368 AS b 
        ->              ON b.id = a.id
        ->          UNION ALL
        ->          SELECT a.id
        ->               , a.firstName
        ->               , a.lastName
        ->               , SUM(b.fileSize) 
        ->               , COUNT(*)    
        ->            FROM roster_cs368 AS a
        ->          INNER
        ->            JOIN atmp_cs368 AS b 
        ->              ON b.id = a.id
        ->          ) AS u
        -> ORDER 
        ->     BY TotalBytes DESC LIMIT 5;
    +----+-----------+----------+------------+--------------+
    | id | firstName | lastName | TotalBytes | TotalAverage |
    +----+-----------+----------+------------+--------------+
    |    | NULL      | NULL     |     687051 |    9411.6575 |
    +----+-----------+----------+------------+--------------+
    1 row in set (0.00 sec)
    No names but bytes and still a single row. I truly appreciate your help.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    i am such an idiot...
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             GROUP
                 BY a.id
                  , a.firstName
                  , a.lastName
             UNION ALL
             SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             GROUP
                 BY a.id
                  , a.firstName
                  , a.lastName
             ) AS u
    GROUP
        BY id
         , firstName
         , lastName          
    ORDER 
        BY TotalBytes DESC LIMIT 5
    Last edited by r937; December 2nd, 2012 at 06:44 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    21
    Rep Power
    0
    Originally Posted by r937
    i am such an idiot...
    Code:
    SELECT id
         , firstName
         , lastName
         , SUM(SubTotalBytes) AS TotalBytes
         , SUM(SubTotalBytes) /
                SUM(SubCount) AS TotalAverage
      FROM ( SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) AS SubTotalBytes
                  , COUNT(*)      AS SubCount
               FROM roster_cs368 AS a
             INNER
               JOIN htmp_cs368 AS b 
                 ON b.id = a.id
             GROUP
                 BY a.id
                  , a.firstName
                  , a.lastName
             UNION ALL
             SELECT a.id
                  , a.firstName
                  , a.lastName
                  , SUM(b.fileSize) 
                  , COUNT(*)    
               FROM roster_cs368 AS a
             INNER
               JOIN atmp_cs368 AS b 
                 ON b.id = a.id
             GROUP
                 BY a.id
                  , a.firstName
                  , a.lastName
             ) AS u
    GROUP
        BY id
         , firstName
         , lastName          
    ORDER 
        BY TotalBytes DESC LIMIT 5
    Thank You so much!

IMN logo majestic logo threadwatch logo seochat tools logo