CODEWITHSUNDEEP
mysqlsql
Matt Schnelker
Matt Schnelker

Reputation: 1

Group by Sub Query?

I have 3 tables (schema below) and I'm trying to determine how many customers have a flag per category. When I try and use a sub query it just keeps counting how many people have a flag regardless of the category. I've tried adding the group by in the sub query but that doesn't seem to help. I'm sure this is more simple than I'm making it.

*In addition, I need to total count of customers in the same output which I think requires it to have a sub query

SELECT p.category, (SELECT count(c.customer_id) from Customer c where c.flag = 'y') as Flagged
from Product p
LEFT JOIN `Orders` o ON p.product_id = o.product_id
LEFT JOIN `Customer` c ON o.customer_id = c.customer_id
group by category;

**Schema SQL**

CREATE TABLE IF NOT EXISTS `Customer` (
  `customer_id` varchar(6),
  `name` varchar(6),
  `flag` varchar(1),
  PRIMARY KEY (`customer_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO Customer VALUES ('000001', 'matt', 'n');
INSERT INTO Customer VALUES ('000002', 'julia', 'y');
INSERT INTO Customer VALUES ('000003', 'carol', 'n');
INSERT INTO Customer VALUES ('000004', 'Riggs', 'n');
  
  CREATE TABLE IF NOT EXISTS `Orders` (
  `order_id` varchar(3),
  `order_item_id` varchar(3),
  `customer_id` varchar(6),
  `date` date,
  `product_id` varchar(4),
  PRIMARY KEY (`customer_id`,`product_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO Orders VALUES ('aaa', 'xxx', '000001','1.1.2018',"prd1");
INSERT INTO Orders VALUES ('bbb', 'yyy', '000001','1.1.2018',"prd2");
INSERT INTO Orders VALUES ('ccc', 'zzz', '000002','1.1.2018',"prd3");
INSERT INTO Orders VALUES ('ddd', 'www', '000003','1.1.2018',"prd4");

CREATE TABLE IF NOT EXISTS `Product` (
  `product_id` varchar(4),
  `name` varchar(6),
  `category` varchar(7),
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO Product VALUES ('prd1', 'nam1', 'Speaker');
INSERT INTO Product VALUES ('prd2', 'nam2', 'Speaker');
INSERT INTO Product VALUES ('prd3', 'nam3', 'Phone');
INSERT INTO Product VALUES ('prd4', 'nam4', 'Phone');

Upvotes: 0

Views: 58

Answers (1)

ggordon
ggordon

Reputation: 10035

You may try the following amendment.

It uses LEFT JOIN to ensure that we will capture all product categories. Moreover it joins customers on c.customer_id = o.customer_id AND c.flag='y' ensuring we only capture flagged customers. Finally it uses COUNT(c.customer_id) to count the flagged customers. If there are no flagged customers for a product category that customer_id will be NULL and count will ignore nulls, thereby only counting flagged customer ids.

SELECT
    p.category, COUNT(c.customer_id) as Flagged
FROM
    Product p
LEFT JOIN
    Orders o ON p.product_id=o.product_id
LEFT JOIN
    Customer c ON c.customer_id = o.customer_id AND
                  c.flag='y'
GROUP BY
    p.category;
category Flagged
Speaker 0
Phone 1

View on DB Fiddle

Edit 1: Also including total customers.

SELECT
    p.category,
    SUM(IF(c.flag='y',1,0)) as Flagged,
    COUNT(DISTINCT c.customer_id) as total_customers
FROM
    Product p
LEFT JOIN
    Orders o ON p.product_id=o.product_id
LEFT JOIN
    Customer c ON c.customer_id = o.customer_id 
                  
GROUP BY
    p.category;
category Flagged total_customers
Speaker 0 1
Phone 1 2

View on DB Fiddle

Let me know if this works for you.

Upvotes: 1

Related Questions