Get comma separated list using sub query in mysql

Question

I have display data of available components quantity and cost of inventory history upto specific date. its works fine using below query.

SELECT s.part_id,
       sum(s.updated_quantity),
       p.item_code,
       sum(
             (SELECT (s.updated_quantity * cost)
              FROM inventory
              WHERE inventory.id=s.inv_id)) AS tcost
FROM status_history AS s,
     inventory AS i,
     part_master AS p
WHERE s.action='add'
  AND DATE(s.date_created)<='2013-04-09'
  AND i.currency_id=1
  AND s.inv_id=i.id
  AND s.part_id=p.id
GROUP BY s.part_id

I also want to display location name of components separated by comma in single field. To get my desire result i have tried below query but it returns only one locations name instead of comma separated string of multiple location names.

SELECT s.part_id,
       sum(s.updated_quantity),
       p.item_code,
       sum(
             (SELECT (s.updated_quantity * cost)
              FROM inventory
              WHERE inventory.id=s.inv_id)) AS tcost,
       CONCAT_WS(',',
                   (SELECT name
                    FROM location_master
                    WHERE id=i.location_id)) AS LOCATION
FROM status_history AS s,
     inventory AS i,
     part_master AS p
WHERE s.action='add'
  AND DATE(s.date_created)<='2013-04-09'
  AND i.currency_id=1
  AND s.inv_id=i.id
  AND s.part_id=p.id
GROUP BY s.part_id

Answer 1

Look at the following example:

mysql> select Country,Abbreviation from Country;
+--------------------------+--------------+
| Country                  | Abbreviation |
+--------------------------+--------------+
| INDIA                    | ID           |
| Canada                   | CAN          |
| Australiya               | AUS          |
| United Kingdom           | UK           |
| United States Of America | USA          |
| PAKISTAN                 | PAK          |
| MILAN                    | Panchal      |
| MILAN                    | Panchal      |
| JAPAN                    | JP           |
+--------------------------+--------------+
9 rows in set (0.00 sec)

There are 2 records for Panchal

Group by Abbreviation will show only 1 record.

mysql> select Country,Abbreviation from Country group by Abbreviation;

+--------------------------+--------------+
| Country                  | Abbreviation |
+--------------------------+--------------+
| Australiya               | AUS          |
| Canada                   | CAN          |
| INDIA                    | ID           |
| JAPAN                    | JP           |
| PAKISTAN                 | PAK          |
| MILAN                    | Panchal      |
| United Kingdom           | UK           |
| United States Of America | USA          |
+--------------------------+--------------+

GROUP_CONCAT on Country will show comma separated values.

mysql> select GROUP_CONCAT(Country),Abbreviation from Country group by Abbreviation;

+--------------------------+--------------+
| GROUP_CONCAT(Country)    | Abbreviation |
+--------------------------+--------------+
| Australiya               | AUS          |
| Canada                   | CAN          |
| INDIA                    | ID           |
| JAPAN                    | JP           |
| PAKISTAN                 | PAK          |
| MILAN,MILAN              | Panchal      |
| United Kingdom           | UK           |
| United States Of America | USA          |
+--------------------------+--------------+

Look at the 6th Record from top. As it contain the Comma separated values.

To know more about GROUP_CONCAT() function click here

Answer 2

Below query Display duplicate data :

select 
s.part_id,
sum(s.updated_quantity),
p.item_code,
sum(
     (select (s.updated_quantity * cost) from inventory where inventory.id=s.inv_id)) 
      as tcost,
GROUP_CONCAT(name) as location 
from status_history as s,
inventory as i,
part_master as p,
location_master as l 
where s.action='add' and 
DATE(s.date_created)<='2013-04-09' and 
i.currency_id=1 and 
s.inv_id=i.id and 
s.part_id=p.id and 
l.id=i.location_id 
group by s.part_id

Below query Display distinct data:

select 
s.part_id, 
sum(s.updated_quantity),
p.item_code,
sum(
     (select(s.updated_quantity * cost) from inventory where inventory.id=s.inv_id))
       as tcost,
GROUP_CONCAT(distinct(name)) as location 
from status_history as s,
inventory as i,
part_master as p,
location_master as l 
where s.action='add' and 
DATE(s.date_created)<='2013-04-09' and 
i.currency_id=1 and 
s.inv_id=i.id and 
s.part_id=p.id and 
l.id=i.location_id 
group by s.part_id