MYSQL SUM(column) question

[devil123]

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

[raf]

i don't think you can do this in one query.

is the data changing a lott? i can't imagine it is, so i would write a script to compute the sum() for each city, and then store this in the city-table

[jahred]

can anyone tell me how to sum int in table rows