Grouping in SQL

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Grouping in SQL

Marc Vos-3
Hello all,

Does anybody know how to get values from a group into one column with one
SQL statement? To illustrate what I mean:

Record set:

Houseno    Street          Inhabitant
---------------------------------------
45             Onestreet    Wife
45             Onestreet    Kid
45             Onestreet    Me


What I want is this:

Houseno    Street          Inhabitant
---------------------------------------
45             Onestreet     Wife, Kid, Me

In one SQL statement. No [records; ... /records].


Is it something like SELECT H,S,I FROM TABLE GROUP BY H,S ..... and then?


Thanks,

-Marc Vos
* * * * * * * * * * * * * * * * * * * * * * * *
Goodyear Dunlop Tires Germany GmbH
Private:  http://marc.vos.net/
* * * * * * * * * * * * * * * * * * * * * * * *




--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Grouping in SQL

James Harvard
This function is available in MySQL 4.1 (IIRC). Look in the MySQL manual under 'functions for use with group by', or some such similar heading.

James Harvard

>Does anybody know how to get values from a group into one column with one
>SQL statement? To illustrate what I mean:
>
>Record set:
>
>Houseno    Street          Inhabitant
>---------------------------------------
>45             Onestreet    Wife
>45             Onestreet    Kid
>45             Onestreet    Me
>
>
>What I want is this:
>
>Houseno    Street          Inhabitant
>---------------------------------------
>45             Onestreet     Wife, Kid, Me

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Grouping in SQL

Olivier Miossec
In reply to this post by Marc Vos-3

Marc Vos ?crit:

> Hello all,
>
> Does anybody know how to get values from a group into one column with one
> SQL statement? To illustrate what I mean:
>
> Record set:
>
> Houseno    Street          Inhabitant
> ---------------------------------------
> 45             Onestreet    Wife
> 45             Onestreet    Kid
> 45             Onestreet    Me
>
>
> What I want is this:
>
> Houseno    Street          Inhabitant
> ---------------------------------------
> 45             Onestreet     Wife, Kid, Me
>
> In one SQL statement. No [records; ... /records].
>
>
> Is it something like SELECT H,S,I FROM TABLE GROUP BY H,S ..... and then?
>
>
> Thanks,


With Mysql 4.1.x you can use the GROUP_CONCAT expression

SELECT Houseno,Street, GROUP_CONCAT(Inhabitant) FROM tablename
GROUP BY Houseno

It returns a string result with the concatenated non-NULL  values from a
group. Values are separed by , by default.
       


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Grouping in SQL

Michael Coninx
In reply to this post by Marc Vos-3
Marc Vos wrote:

>Hello all,
>
>Does anybody know how to get values from a group into one column with one
>SQL statement? To illustrate what I mean:
>
>Record set:
>
>Houseno    Street          Inhabitant
>---------------------------------------
>45             Onestreet    Wife
>45             Onestreet    Kid
>45             Onestreet    Me
>
>What I want is this:
>
>Houseno    Street          Inhabitant
>---------------------------------------
>45             Onestreet     Wife, Kid, Me
>
>In one SQL statement. No [records; ... /records].
>
>Is it something like SELECT H,S,I FROM TABLE GROUP BY H,S ..... and then?
>  
>

This only works in MySql 4.1 and higher!!!

select houseno, street, group_concat(distinct inhabitant separator ', ')
as inhabitants from table_name where houseno = 45  group by houseno

With kind regards,

Michael

>Thanks,
>
>-Marc Vos
>* * * * * * * * * * * * * * * * * * * * * * * *
>Goodyear Dunlop Tires Germany GmbH
>Private:  http://marc.vos.net/
>* * * * * * * * * * * * * * * * * * * * * * * *
>
>  
>


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Grouping in SQL

Marc Vos-3
In reply to this post by Marc Vos-3
James, Oliver and Michael,

Just what I was looking for. Thanks very much for the quick answers!

Regards,

Marc Vos


On 08-06-2005 14:00, "Michael Coninx" <[hidden email]> wrote:

> Marc Vos wrote:
>
>> Hello all,
>>
>> Does anybody know how to get values from a group into one column with one
>> SQL statement? To illustrate what I mean:
>>
>> Record set:
>>
>> Houseno    Street          Inhabitant
>> ---------------------------------------
>> 45             Onestreet    Wife
>> 45             Onestreet    Kid
>> 45             Onestreet    Me
>>
>> What I want is this:
>>
>> Houseno    Street          Inhabitant
>> ---------------------------------------
>> 45             Onestreet     Wife, Kid, Me
>>
>> In one SQL statement. No [records; ... /records].
>>
>> Is it something like SELECT H,S,I FROM TABLE GROUP BY H,S ..... and then?
>>  
>>
>
> This only works in MySql 4.1 and higher!!!
>
> select houseno, street, group_concat(distinct inhabitant separator ', ')
> as inhabitants from table_name where houseno = 45  group by houseno
>
> With kind regards,
>
> Michael
>
>> Thanks,
>>
>> -Marc Vos
>> * * * * * * * * * * * * * * * * * * * * * * * *
>> Goodyear Dunlop Tires Germany GmbH
>> Private:  http://marc.vos.net/
>> * * * * * * * * * * * * * * * * * * * * * * * *
>>
>>  
>>
>



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage