MySql - Getting total number from two groups of records from one table

Question

I am on Mysql version 5.5. I have two tables - product, ordr.

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ordr` (
  `id` int(11) NOT NULL,
  `product_id` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is how they are populated -

insert into product values (20, 'pen');

insert into ordr values (100, 20, 'queue', 5);
insert into ordr values (110, 20, 'queue', 5);
insert into ordr values (120, 20, 'pending', 10);
insert into ordr values (130, 20, 'pending', 10);
insert into ordr values (140, 20, 'pending', 10);

I want to get the total quantity of products that are in two different statuses. For the test data above data, I want to see 10 for queue quantity and 30 for pending quantity.

When I run

select p.name, sum(o.qty) as queue_qty, sum(o2.qty) as pending_qty
from product p
left outer join ordr o on o.product_id = p.id and o.status = 'queue'
left outer join ordr o2 on o2.product_id = p.id and o2.status = 'pending'
where p.id = 20;

I get

name - pen
queue_qty - 30
pending_qty - 60

Can someone help me fix this SQL?

Jacobm001 · Accepted Answer · 2017-11-18T00:02:19.377

0

You need to aggregate the information before it's joined. Since you have two 1-n relationships, your join logic is duplicating information.

select
  p.name
  , o.qty as qty_queue
  , o2.qty as qty_pending
from
  product p
  join (
    select
      product_id
      , sum(qty) as qty
    from
      ordr
    where
      ordr.status = 'queue'
    group by
      product_id
  ) o
    on p.id = o.product_id
  join (
    select
      product_id
      , sum(qty) as qty
    from
      ordr
    where
      ordr.status = 'pending'
    group by
      product_id
  ) o2
    on p.id = o2.product_id
group by
  p.name

edited Nov 18 '17 at 00:02

answered Nov 17 '17 at 23:52

Jacobm001

4,431
4
30
51

Thanks for the quick response. My use case is such that I need the result set in the form of one record for product, with the sums as columns in the record. The question is a simplified version of the use case. Is there a way to accomplish that? – Surya Nov 17 '17 at 23:59
@Surya: I realized that once I had answered. It's rather tedious to do so with MySQL, but it's doable. Please see the updated answer. – Jacobm001 Nov 18 '17 at 00:02

MySql - Getting total number from two groups of records from one table

1 Answers1