SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL SUM(column) question

    Hi All

    I have a select SUM question

    This query works sort of:

    SELECT population AS population, city AS city, roofheight AS skyline, SUM(roofheight) AS addheights FROM building WHERE population > 0 Group By city ORDER BY roofheight DESC limit 30



    which pulls

    +------------+---------------------+---------+------------+
    | population | city | skyline | addheights |
    +------------+---------------------+---------+------------+
    | 11850000 | London | 303.00 | 27092.90 |
    | 1998337 | Birmingham | 187.00 | 12037.00 |
    | 249884 | Brighton | 128.00 | 1225.00 |
    | 1362034 | Liverpool | 125.00 | 6457.00 |
    | 1906995 | Manchester | 118.00 | 8895.30 |
    | 495781 | Dublin | 117.00 | 1351.45 |
    | 1698841 | Leeds | 80.00 | 5817.00 |
    | 272129 | Cardiff | 80.00 | 889.00 |
    | 279237 | Belfast | 80.00 | 80.00 |
    | 797021 | Newcastle upon Tyne | 77.00 | 2180.75 |
    | 266543 | Stoke on Trent | 75.00 | 115.00 |
    | 289376 | Bradford | 66.00 | 596.00 |
    | 241443 | Kingston upon Hull | 64.00 | 1273.00 |
    | 239358 | Wolverhampton | 62.00 | 62.00 |
    | 278958 | Sunderland | 55.00 | 55.00 |
    | 240467 | Plymouth | 53.00 | 492.00 |
    | 287648 | Doncaster | 40.00 | 290.00 |
    | 1052784 | Glasgow | 0.00 | 1592.90 |
    | 381618 | Bristol | 0.00 | 2373.60 |
    | 729223 | Sheffield | 0.00 | 3350.00 |
    | 270005 | Nottingham | 0.00 | 0.00 |
    | 448624 | Edinburgh | 0.00 | 0.00 |
    | 304746 | Coventry | 0.00 | 1128.00 |
    | 283578 | Leicester | 0.00 | 1309.00 |
    +------------+---------------------+---------+------------+

    The problem is i need the SUM of the top 30 roofheights of each city with a population > 0 ONLY, the above SUMs ALL height to that city!!!!!!!! Limit 30 only works for results, is there a way round this????


    thanks


    Jay

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select population
         , city
         , sum(roofheight) as addheights
      from (
           select t1.population
                , t1.city
                , t1.roofheight 
             from building as t1
           inner
             join building as t2
               on t1.city = t2.city
              and t1.roofheight <= t2.roofheight
            where t1.population > 0 
           group 
               by t1.population
                , t1.city
                , t1.roofheight 
           having count(*) <= 30    
           ) as top30
    group
        by population
         , city
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •