Hi Rob!
Of course you can group by "bands" as you stated. You can use many GROUP
columns to widen the grouping, for example, one can be tempted to write
"GROUP BY name, age" in your case, but this is not what you like.
The problem here is to transform the column "age" so that the grouping
is done as you like. For example, you can use CASE statements to convert the
real age into "bands", ranging from 1 to 4:
SELECT
CASE
WHEN age < 15 THEN 1
WHEN age BETWEEN 15 AND 29 THEN 2
WHEN age BETWEEN 30 AND 45 THEN 3
WHEN age > 45 THEN 4
END AS age_range,
name,
COUNT(*) as how_many
FROM test
GROUP BY age_range, name
Note that in the above SELECT, we feed the GROUP BY a number
representing the age range (age_range).
The SELECT will produce the following result, as close as possible to
what you stated:
age_range Name How_Many
----------+-------+----------
1 | James | 1
1 | John | 2
3 | John | 1
3 | Rob | 3
4 | John | 2
(Please Note the lack of the "age_range" number 2: 15-29).
Hope it helps.
Jose Miguel.
----- Original Message -----
From: "Rob" <robmail@stripped>
To: "MySql" <mysql@stripped>
Sent: Tuesday, May 27, 2003 10:42 AM
Subject: group by
> Hi all,
>
> I have a question regarding group by. Is it possible to group by "bands"?
> What I mean is this. Say I have the following table:
>
> ID | Age | Name
> ---------------
> 1 | 30 | Rob
> 1 | 12 | John
> 1 | 13 | James
> 1 | 14 | John
> 1 | 32 | Rob
> 1 | 33 | John
> 1 | 34 | Rob
> 1 | 67 | John
> 1 | 68 | John
>
> Say now I want a count of names grouped by name and by, NOT AGE, but age
> groups i.e. 0 - 14, 15 - 29, etc., so I should get an answer like
>
> Age Name Count
>
> 0 - 14 John 2
> 0 - 14 James 1
> 15 - 29 - -
> 30 - 45 Rob 3
>
> etc.
>
> Is this possible?
>
>
> ---
> Rob
>
> **************************
> Rob Cherry
> mailto:rob@stripped
> +27 21 447 7440
> Jam Warehouse RSA
> Smart Business Innovation
> http://www.jamwarehouse.com
> **************************
>
>
>
> All information contained in this email is confidential and may be used by
the intended recipient only.
>
>
----------------------------------------------------------------------------
----
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
