Results 1 to 4 of 4
  1. #1

    Use of count(*) in mysql query

    Hi, i have this query:

    PHP Code:
    SELECT counta.name ) AS cnt
        
    AVG( ( a.rank + ( 1.5 a.ballots ) ) * 
            ( ( 
    LOG(b.powerrank) / ) ) + cnt 
            as 
    total,
        
    a.name,
        
    a.school,
        
    a.tourny,
        
    a.rank,
        
    a.side,
        
    a.team,
        
    a.ballots,
        
    b.id,
        
    b.year,
        
    b.name,
        
    b.powerrank
        FROM atty a
    tourny b
        WHERE a
    .tourny=b.id
        GROUP BY a
    .name
        ORDER BY total DESC 
    I want to be able to use the value of cnt in that AVG formula, but it's telling me I can't. how would i do this?

    tia.

  2. #2
    Join Date
    Apr 2004
    Location
    malaysia
    Posts
    44

    y not like this, y u wana reassign?

    SELECT AVG( ( a.rank + ( 1.5 * a.ballots ) ) *
    ( ( LOG(b.powerrank) / 3 ) ) + count( a.name ) )
    as total....

  3. #3
    I've tried that and mysql says it's an invalid use of the group function.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,715
    Because it would need to go through the table twice -- once to gather the count, then again to deal with the average. However, increasing all points in an average by a constant will simply increase the average by that constant, so this should accomplish the same goal:

    SELECT count( a.name ) AS cnt,
    AVG( ( a.rank + ( 1.5 * a.ballots ) ) *
    ( ( LOG(b.powerrank) / 3 ) ) )
    + count( a.name ) as total,
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •