group by problem....

[anujgarg]

I have a table structure:

 

id  comp_name

1      a

2      a

3      a

4      b

5      b

6      a

 

1) Now, I am running a query which returns the company name and total number of companies with same name in group by clause ie a(4) and b(2).

 

2) what I want to do is, to get the individual IDs corresponding to each company name or comma separated ie a (4) - 1,2,3,6

and b(2) - 4,5

 

I have covered the step 1) but how do I cover the step 2).

All I am getting is the same IDs in both cases:

ie a(4) - 1,2,3,4,5,6

b(2) - 1,2,3,4,5,6

 

How to solve this problem?

 

TIA

[Mchl]

Showing us how you're getting these result would help.

[PFMaBiSmAd]

Use the mysql GROUP_CONCAT() function.

[sasa]

select comp_name, COUNT(id) as num, GROUP_CONCAT(id) as ids FROM table_name GROUP BY name

[anujgarg]

sasa, I applied your query and I found the following:

 

 

company_name num ids

                    2 [bLOB - 5 B]

grger                 1    [bLOB - 2 B]

xgfdg                 3    [bLOB - 8 B]

xgfdgdfdfdf       1    [bLOB - 2 B]

 

how do i get the numeric values in ids....

[Stryves]

Just add it to the query.

 

select comp_name, id, COUNT(id) as num, GROUP_CONCAT(id) as ids FROM table_name GROUP BY name

[anujgarg]

Stryves

 

This is also not what I need.

I give you the statements what I am writing:

 

SELECT *, count(*) as count FROM table GROUP BY comp_name

 

foreach ($agentList->result() as $search)

{

    echo "<tr><td><strong>".$search->id . $search->comp_name. search->count</strong></td></tr>";

echo "<tr><td> </td></tr>";

}

 

SELECT id FROM table

 

foreach ($agentId->result() as $searchId)

{

$jobId .= $searchId->jobs_id . ",";

}

 

The first query returns a(4) and b(2) as per my example.

The second query returns 1,2,3,4,5,6 in both cases which I need with their corresponding names and Ids.

 

 

[sasa]

select comp_name, COUNT(id) as num, CAST(GROUP_CONCAT(id) AS CHAR) as ids FROM table_name GROUP BY name

[anujgarg]

nice sasa, thanks a lot...

 

It worked