Adjust sql query to get count with group_concat

Question

I have the following query which lists orders along with items ordered.

select 
    `orders`.*, 
    DATE_FORMAT(orders.created_at, '%d.%m.%Y %h:%i%p') as date, 
    `oi`.`items` 
        from `orders` 
    inner join 
        (
            select 
                order_id,
                count(item_name) as count,
                group_concat(item_name SEPARATOR ",") as items 
                    from orders_items 
                    group by order_id
        ) 
    as oi on `oi`.`order_id` = `orders`.`id`

My tables are setup like so:

orders

id
1
2
3

orders_items

id    order_id    item_name
1     1           Class Voucher
2     1           Class Voucher
3     1           Class Voucher
4     1           The Cook Book

In the current query, the items column looks something like this when I output it:

Class Voucher,Class Voucher,Class Voucher,The Cook Book

How can I alter it so that each item has a count next to it, instead of listing out each item name. So I want items to contain this instead:

3 x Class Voucher
1 x The Cook Book

Thanks in advance.

query needs to be adjusted to exclude orders.created_at ( this is not currently part of your posted structure ) — amdixon
– amdixon, Commented Sep 11, 2015 at 9:26
FYI, you should use COUNT(*) unless you need to exclude null values from the count (this mostly comes up when counting rows in a LEFT JOIN). — Barmar
– Barmar, Commented Sep 11, 2015 at 9:31

Thorsten Kettner · Accepted Answer · 2015-09-11 09:27:01Z

2

You get there in two steps: First count the items, then get all items with their count per order:

select 
  orders.*, 
  DATE_FORMAT(orders.created_at, '%d.%m.%Y %h:%i%p') as date, 
  oi.items 
from orders 
inner join 
(
  select order_id,  group_concat(concat(cnt, ' x ', item_name)) as items 
  from
  (
    select order_id, item_name, count(*) as cnt
    from orders_items 
    group by order_id, item_name
  ) as counted
  group by order_id
) as oi on oi.order_id = orders.id;

answered Sep 11, 2015 at 9:27

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

V4n1ll4 Over a year ago

Thanks, thats what I was looking for!

mastisa · Accepted Answer · 2018-08-12 11:45:18Z

0

Further to the aforementioned query:

select    orders.*,    DATE_FORMAT(orders.created_at, '%d.%m.%Y
%h:%i%p') as date,    oi.items  from orders  inner join  (   select
order_id,  group_concat(concat(cnt, ' x ', item_name)) as items   
from   (
select order_id, item_name, count(*) as cnt
from orders_items 
group by order_id, item_name   ) as counted   group by order_id ) as oi on oi.order_id = orders.id;

I would add a

Having Count(*)>0

to eliminate any items with a 0 quantity

edited Aug 12, 2018 at 11:45

mastisa

2,1413 gold badges25 silver badges42 bronze badges

answered Aug 12, 2018 at 9:10

user3104783

192 bronze badges

Collectives™ on Stack Overflow

Adjust sql query to get count with group_concat

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related