I’ve gone snow blind, I’m trying to work out the total sum of unique accounts within a table but just not getting there.
The table contains an ACCOUNT_ID and each account may have a single or multiple pieces of equipment, each piece of equipment will have a number of associated services. If the Account has multiple services it will consist of multiple records containing the Same ACCOUNT_ID but different service values and equipment values so essentially a unique account may have numerous records. I’m trying to get a count of the services at an account level rather than an equipment level.
Trying aggregate functions such as SUM over () I get back the counts of the services correctly however these are not unique at ACCOUNT_ID. As a basic example here is some data;
ACCOUNT_ID EQUIPMENT_ID SERVICE_NAME 12345 101010 SERVICE1 12345 101010 SERVICE2 12346 102010 SERVICE1 12346 102011 SERVICE1 12347 103010 SERVICE1 12347 104010 SERVICE1 12347 104010 SERVICE2 12347 104010 SERVICE3 12347 104011 SERVICE1 12347 104011 SERVICE2 12347 104011 SERVICE3
As above there are 3 Unique Accounts so I’d like my results to look like below;
SERVICE1 3 SERVICE2 2 SERVICE3 1
Whats the best way to achieve this?
Many Thanks
Advertisement
Answer
You can use aggregation and count(distinct):
select service_name, count(distinct account_id) cnt from mytable group by service_name