Need "Top 5" Query Help...

Dev Shed Forums Sponsor:

December 2nd, 2012, 02:28 PM

csharp100

Registered User

Join Date: Jul 2010

Posts: 21

Time spent in forums: 14 h 13 m 13 sec
Reputation 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.

December 2nd, 2012, 04:04 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

December 2nd, 2012, 04:32 PM

csharp100

Registered User

Join Date: Jul 2010

Posts: 21

Time spent in forums: 14 h 13 m 13 sec
Reputation Power: 0

Quote:

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.

December 2nd, 2012, 05:48 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

Quote:

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

December 2nd, 2012, 06:02 PM

csharp100

Registered User

Join Date: Jul 2010

Posts: 21

Time spent in forums: 14 h 13 m 13 sec
Reputation Power: 0

Quote:

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.

December 2nd, 2012, 06:41 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

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.

December 2nd, 2012, 06:51 PM

csharp100

Registered User

Join Date: Jul 2010

Posts: 21

Time spent in forums: 14 h 13 m 13 sec
Reputation Power: 0

Quote:

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!

Need "Top 5" Query Help...

Developer Shed Advertisers and Affiliates