SQL query select with ranges

Question

I have one table like this...

+---------+------------+------+-------+
|  City   | NamePeople | Age  | Money |
+---------+------------+------+-------+
| Paris   | John       |  10  |  200  |
| London  |  Marie     |  21  |  300  |
| Paris   |  Kevin     |  50  |  250  |
| Paris   |  Julia     |  45  |  150  |
+---------+------------+------+-------+

I need a result with ranges , something like this

+---------------+------------+-----------------------+-------------------------+
|      City     | Sum(Money) | CountPeopleWithAge<30 | CountPeopleWithAge>30   |
+---------------+------------+-----------------------+-------------------------+
|   Paris       |       600  |                    1  |                      2  |
|   London      |       300  |                    1  |                      0  |
+---------------+------------+-----------------------+-------------------------+

How do I do this with sql select?

Thanks.

Please include the code you've tried and what went wrong. `Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results.` — showdev, Sep 16 '13 at 20:52
@Laurence I think there's some more issues with the expected output http://sqlfiddle.com/#!3/d964b/2 — Conrad Frix, Sep 16 '13 at 21:02
How about this? http://stackoverflow.com/questions/1400078/is-it-possible-to-specify-condition-in-count — Max Sherman, Sep 16 '13 at 20:52

score 4 · Accepted Answer · answered Sep 16 '13 at 20:53

4

Select
    City,
    Sum(Money),
    Sum(Case When Age < 30 Then 1 Else 0 End),
    Sum(Case When Age > 30 Then 1 Else 0 End)
From
    table
Group By
    City

answered Sep 16 '13 at 20:53

Laurence

10,896
1
25
34

Laurence' answer looks the best, but don't forget about the EXACTLY 30 years old people. – Andrew Sep 16 '13 at 20:58

score 0 · Answer 2 · answered Sep 16 '13 at 20:56

0

You can use this Query,

SELECT City, 
    SUM(Money), 
   SUM(case when Age < 30 then 1 else 0 end),
   SUM(case when Age > 30 then 1 else 0 end)
FROM tableA
GROUP BY City

answered Sep 16 '13 at 20:56

Viji

2,629
1
18
30

SQL query select with ranges

2 Answers2