CODEWITHSUNDEEP
sqlcountdistinct
dc4teg
dc4teg

Reputation: 109

GROUP BY First_Name but show COUNT of the corresponding Last_Name SQL

I have a SQL statement that will count an occurence and group by First_Name, but for other purposes I need to know a COUNT of how many family members are customers as well by using the same last name. This is the code I have written.

SELECT a.Last_Name, a.First_Name,
SUM(CASE WHEN b.ITM = 'P_10' THEN 1 ELSE 0 END) AS P_10_COUNT
FROM CUST_PROFILE
INNER JOIN ORDER b
ON a.ID = b.ID
GROUP BY a.First_Name, a.Last_Name;

I have a rough idea (maybe) of how I need to write the code see below....

SELECT a.Last_Name, a.First_Name,
SUM(CASE WHEN b.ITM = 'P_10' THEN 1 ELSE 0 END) AS P_10_COUNT,
COUNT(c.Name_Count)
FROM CUST_PROFILE
INNER JOIN ORDER b
ON a.ID = b.ID
LEFT JOIN (select Last_Name, count(DISTINCT First_Name) Name_Count
from CUST_PROFILE
GROUP BY Last_Name) c
GROUP BY a.First_Name, a.Last_Name;

Upvotes: 0

Views: 124

Answers (2)

dc4teg
dc4teg

Reputation: 109

SELECT a.Last_Name, a.First_Name,
SUM(CASE WHEN b.ITM = 'P_10' THEN 1 ELSE 0 END) AS P_10_COUNT,
c.Name_Count
FROM CUST_PROFILE
INNER JOIN ORDER b
ON a.ID = b.ID
LEFT JOIN (select Last_Name, count(DISTINCT First_Name) Name_Count
from CUST_PROFILE
GROUP BY Last_Name) c ON c.Last_Name = a.Last_Name
GROUP BY a.First_Name, a.Last_Name,c.Name_Count;

Upvotes: 0

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61459

It all looks all right you just need to JOIN your subselect

SELECT a.Last_Name, a.First_Name,
SUM(CASE WHEN b.ITM = 'P_10' THEN 1 ELSE 0 END) AS P_10_COUNT,
COUNT(c.Name_Count)
FROM CUST_PROFILE
INNER JOIN ORDER b
ON a.ID = b.ID
LEFT JOIN (select Last_Name, count(DISTINCT First_Name) Name_Count
from CUST_PROFILE
GROUP BY Last_Name) c ON c.Last_Name = a.Last_Name
GROUP BY a.First_Name, a.Last_Name;

Upvotes: 1

Related Questions