Counting the Sum of Unique Accounts

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

Answer

You can use aggregation and count(distinct):

select service_name, count(distinct account_id) cnt from mytable group by service_name