Here is a sample data to explain my case,
CompanyInfo:
Name, Company, Location, Completed, Pending
Andy AA Home 4 2
Jim AA Office 3 3
Dahli AA Home 4 2
Monica AA Home 4 2
Chandler AA Home-Office 1 0
Ashley AA Home-Office 1 0
The last three columns have duplicated information and I am trying to obtain count of location, completed and pending which are bound to each other. So the output would look something like below,
Company, Count(Locations), Count( Completed+Pending > 0),
AA 3 3
Why Count( Completed+Pending > 0) is 3? there are just three unique combinations of Home, Office and home-office columns where sum of completed+pending is > 0.
I did try below, but it gives me (AA, 3, 6) since it is processing all the 6 rows to obtain the count.
select Company,
count(distinct Location),
SUM (
CASE
WHEN (Completed + Pending) > 0 THEN 1
ELSE 0
END)
AS Total
From CompanyInfo
group by Company;
Any pointers?
I think you want a conditional count(distinct)
:
select Company,
count(distinct Location),
count(distinct case when Completed + Pending > 0 then location end)
from CompanyInfo
group by Company;