Mysql
I recently discovered a cool function in MySQL that is nice for doing group by queries where you want to return a column with a comma separated list of data to sum up totals. I learn best from examples so let’s look a 3 table example.
Products
CREATE TABLE products (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
)
Inventory Items
CREATE TABLE inventory_items (
id int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) NOT NULL,
sku varchar(255) NOT NULL,
PRIMARY KEY (id)
)
Orders
CREATE TABLE orders (
id SERIAL,
inventory_item_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
PRIMARY KEY (id)
)
To get a comma separated list of skus along with product names and sum of the amount for the orders, we would use the following SQL;
SELECT p.name, sum(o.amount), GROUP_CONCAT(i.sku)
FROM orders o
LEFT JOIN inventory_items i ON (o.inventory_item_id = i.id)
LEFT JOIN products p ON (p.id = i.product_id)
GROUP BY p.name
+---------+---------------+----------------------------------------------------------------+
| name | sum(o.amount) | GROUP_CONCAT(i.sku) |
+---------+---------------+----------------------------------------------------------------+
| hat | 260 | aaa111,bbb111,bbb111,cc111 |
| shoe | 610 | ddd111,eee111,eee111,eee111 |
| t-shirt | 15749 | fff111,fff111,fff111,ggg111,ggg111,ggg111,ggg111,hhh111,hhh111 |
+---------+---------------+----------------------------------------------------------------+
One annoying thing here is that we have repeating skus. We can use a DISTINCT command for the sku column.
SELECT p.name, sum(o.amount), GROUP_CONCAT(DISTINCT i.sku)
FROM orders o
LEFT JOIN inventory_items i ON (o.inventory_item_id = i.id)
LEFT JOIN products p ON (p.id = i.product_id)
GROUP BY p.id;
+---------+---------------+------------------------------+
| name | sum(o.amount) | GROUP_CONCAT(DISTINCT i.sku) |
+---------+---------------+------------------------------+
| hat | 260 | aaa111,bbb111,cc111 |
| shoe | 610 | ddd111,eee111 |
| t-shirt | 15749 | fff111,ggg111,hhh111 |
+---------+---------------+------------------------------+
Postgres
Postgres does not have the GROUP_CONCAT function but has function called array_agg. Combined with array_to_string, we can mimic the functionality of GROUP_CONCAT.
Products
CREATE TABLE products (
id SERIAL,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
Inventory Items
CREATE TABLE inventory_items (
id SERIAL,
product_id INTEGER NOT NULL,
sku varchar(255) NOT NULL,
PRIMARY KEY (id)
);
Orders
CREATE TABLE orders (
id SERIAL,
inventory_item_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
PRIMARY KEY (id)
)
Our SQL query will look like this:
SELECT p.name, sum(o.amount), array_to_string(array_agg(i.sku), ',')
FROM orders o
LEFT JOIN inventory_items i ON (o.inventory_item_id = i.id)
LEFT JOIN products p ON (p.id = i.product_id)
GROUP BY p.id;
hat | 260 | aaa111,bbb111,bbb111,cc111
shoe | 610 | ddd111,eee111,eee111,eee111
t-shirt | 15749 | fff111,fff111,fff111,ggg111,ggg111,ggg111,ggg111,hhh111,hhh111
Similar to the MySQL example, we can use a distinct to remove duplicates:
SELECT p.name, sum(o.amount), array_to_string(array_agg(DISTINCT i.sku), ',')
FROM orders o
LEFT JOIN inventory_items i ON (o.inventory_item_id = i.id)
LEFT JOIN products p ON (p.id = i.product_id)
GROUP BY p.name ;
hat | 260 | aaa111,bbb111,cc111
shoe | 610 | ddd111,eee111
t-shirt | 15749 | fff111,ggg111,hhh111