CODEWITHSUNDEEP
mysqlsql
Andy P.
Andy P.

Reputation: 69

How to properly using a GROUP BY statement in SQL?

I need to run a query for a lead database, which need to breakdown the store name, total leads for that store, and total converted leads. So far, below is what I got:

SELECT  stores.store_name as Store, COUNT(*) as "Total Lead", 
        (SELECT COUNT(*) as "Converted Lead" FROM leads WHERE lead_status_id = 5) 
FROM "leads" 
INNER JOIN "employee_leads" ON "employee_leads"."lead_id" = "leads"."id" 
INNER JOIN "employees" ON "employees"."id" = "employee_leads"."employee_id" 
INNER JOIN "emp_stores" ON "emp_stores"."employee_id" = "employees"."id" 
INNER JOIN "stores" ON "stores"."id" = "emp_stores"."store_id" 
GROUP BY store_name;

However, this is wrong because the converted part calculated all the converted leads instead of by the certain store. The result I got is below:

 +------------+-------------+----------------+
 | Store      | Total Lead  | Converted Lead |
 +------------+-------------+----------------+
 | Store1     |         10  |            10  |
 | Store2     |         14  |            10  |
 | Store3     |         17  |            10  |
 +------------+-------------+----------------+

In the "Converted Lead" column is currently listed the total converted lead across all stores. The "Converted Lead" column suppose to be 4 for Store1, 3 for Store2, and 4 for Store3. I tried to do the sub-groupby, however, I got a error because there will be more than 1 row for each store. I need the query calculate each store's converted lead, which look something like below:

 +------------+-------------+----------------+
 | Store      | Total Lead  | Converted Lead |
 +------------+-------------+----------------+
 | Store1     |         10  |             4  |
 | Store2     |         14  |             3  |
 | Store3     |         17  |             4  |
 +------------+-------------+----------------+

Can someone point me to the right direction? Thank you

Upvotes: 4

Views: 51

Answers (3)

Hemang A
Hemang A

Reputation: 1012

Please try this.

SELECT  stores.store_name as Store, COUNT(*) as "Total Lead", 
        (SELECT COUNT(*) as "Converted Lead" FROM leads AS SS WHERE SS.id =A.id AND lead_status_id = 5) 
FROM "leads" AS A 
INNER JOIN "employee_leads" ON "employee_leads"."lead_id" = "A"."id" 
INNER JOIN "employees" ON "employees"."id" = "employee_leads"."employee_id" 
INNER JOIN "emp_stores" ON "emp_stores"."employee_id" = "employees"."id" 
INNER JOIN "stores" ON "stores"."id" = "emp_stores"."store_id" 
GROUP BY store_name;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311998

You aren't correlating the subquery with the store name. While this could be done in its where clause, and easier approach would be to count a case expression to only count the leads with the relevant status:

SELECT  stores.store_name as Store, 
        COUNT(*) as "Total Lead", 
        COUNT(CASE lead_status_id WHEN 5 THEN 1 END) AS "Converted Lead"
FROM "leads" 
INNER JOIN "employee_leads" ON "employee_leads"."lead_id" = "leads"."id" 
INNER JOIN "employees" ON "employees"."id" = "employee_leads"."employee_id" 
INNER JOIN "emp_stores" ON "emp_stores"."employee_id" = "employees"."id" 
INNER JOIN "stores" ON "stores"."id" = "emp_stores"."store_id" 
GROUP BY store_name;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46239

You can try to use condition aggregate function instead of subquery.

SELECT  stores.store_name as Store, COUNT(*) as "Total Lead", 
        COUNT(CASE WHEN lead_status_id = 5 THEN 1 END) "Converted Lead" 
FROM "leads" 
INNER JOIN "employee_leads" ON "employee_leads"."lead_id" = "leads"."id" 
INNER JOIN "employees" ON "employees"."id" = "employee_leads"."employee_id" 
INNER JOIN "emp_stores" ON "emp_stores"."employee_id" = "employees"."id" 
INNER JOIN "stores" ON "stores"."id" = "emp_stores"."store_id" 
GROUP BY store_name;

if you are using mysql you can try to use COUNT(lead_status_id = 5)

SELECT  stores.store_name as Store, COUNT(*) as "Total Lead", 
        COUNT(lead_status_id = 5) "Converted Lead" 
FROM "leads" 
INNER JOIN "employee_leads" ON "employee_leads"."lead_id" = "leads"."id" 
INNER JOIN "employees" ON "employees"."id" = "employee_leads"."employee_id" 
INNER JOIN "emp_stores" ON "emp_stores"."employee_id" = "employees"."id" 
INNER JOIN "stores" ON "stores"."id" = "emp_stores"."store_id" 
GROUP BY store_name;

Upvotes: 2

Related Questions