List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:October 6 2005 9:50pm
Subject:Re: Easy or not so easy GROUP BY
View as plain text  
Thanks for the reply,
 
Sorry; I'm using 4.1.11
 
Thanks again.

>>> Pooly <pooly7@stripped> 10/6/05 1:51 AM >>>
2005/10/6, Ed Reed < EReed@stripped >:
> I'm trying to group some sub categories with a concatenated result. I'm trying to get
> the max sub for each item per month. I think it should be fairly easy but it is kicking my
> butt. My data looks like this,
>
> +--------+--------+------+
> | month | item | sub |
> +--------+--------+------+
> | 8 | 5 | NULL |
> +--------+--------+------+
> | 8 | 4 | a |
> +--------+--------+------+
> | 8 | 6 | NULL |
> +--------+--------+------+
> | 8 | 6 | a |
> +--------+--------+------+
> | 8 | 5 | a |
> +--------+--------+------+
> | 8 | 4 | b |
> +--------+--------+------+
> | 9 | 1 | NULL |
> +--------+--------+------+
> | 9 | 2 | NULL |
> +--------+--------+------+
> | 9 | 1 | a |
> +--------+--------+------+
> | 9 | 3 | NULL |
> +--------+--------+------+
> | 9 | 2 | a |
> +--------+--------+------+
> | 9 | 1 | b |
> +--------+--------+------+
> | 9 | 4 | NULL |
> +--------+--------+------+
> | 9 | 4 | a |
> +--------+--------+------+
> | 9 | 2 | b |
> +--------+--------+------+
> | 9 | 1 | c |
> +--------+--------+------+
> | 10 | 1 | NULL |
> +--------+--------+------+
> | 10 | 1 | a |
> +--------+--------+------+
> | 10 | 2 | NULL |
> +--------+--------+------+
>
> I'm not having a problem getting a concatenated result but I am having difficulty
> getting my data grouped correctly. My results should look like this.
> +-----------+
> | MAX Group |
> +-----------+
> | 8-4b |
> +-----------+
> | 8-5a |
> +-----------+
> | 8-6a |
> +-----------+
> | 9-1c |
> +-----------+
> | 9-2b |
> +-----------+
> | 9-3 |
> +-----------+
> | 9-4a |
> +-----------+
> | 10-1a |
> +-----------+
> | 10-2 |
> +-----------+
>
>
> - Thanks in advance
>

So, Max group by month/item ?
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html 
and for concat :
http://dev.mysql.com/doc/mysql/en/string-functions.html 

Since we don't know your MySQL version, I can't give you a precise answer.
HIMH

--
Pooly
Webzine Rock : http://www.w-fenec.org/ 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/mysql?unsub=1 




Thread
Easy or not so easy GROUP BYEd Reed6 Oct
  • Re: Easy or not so easy GROUP BYPooly6 Oct
Re: Easy or not so easy GROUP BYEd Reed6 Oct
Re: Easy or not so easy GROUP BYEd Reed11 Oct
Re: Easy or not so easy GROUP BYEd Reed13 Oct
  • Re: Easy or not so easy GROUP BYSGreen13 Oct