RE: group by

[Rob]

Very cunning !!!

---
Rob

**************************
Rob Cherry
mailto:rob@stripped
[phone]
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


-----Original Message-----
From: Jose Miguel Pérez [mailto:jruiz@stripped]
Sent: Tuesday, May 27, 2003 11:57 AM
To: Rob; MySql
Subject: Re: group by


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
> [phone]
> 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



All information contained in this email is confidential and may be used by the intended
recipient only.