SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Compute stats across tables

    Hi,

    I have this query:

    SELECT AVG(balance) FROM accountsABC

    then I want to collect the same stats for other table (same schema, just different data). Eg: accountsABC, accountsDEF....

    How could I do this with just one query? Thanks.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would have to UNION them together:
    Code:
    SELECT AVG( balance ) 
      FROM t1
    UNION
    SELECT AVG( balance ) 
      FROM t2
    etc.
    Or use subqueries:
    Code:
    SELECT ( SELECT AVG( balance )
               FROM t1 ) AS t1bal,
           ( SELECT AVG( balance )
               FROM t2 ) AS t2bal,
    etc.

  3. #3
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems that until MySQL 4.0 it supports UNION, but my version is 3.23.36. And I'm not sure if MySQL support sub selects (it does, but in a different form I think). Tough luck! A script will do, but a query is certainly nicer

    Thanks Matt


Bookmarks

Posting Permissions

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