Trying to use SQL to group accounts by number of sub-types-CodePudding

I'm using a table that houses account info. These accounts can have between 1 and 6 unique sub types. Currently it only tracks between single and multi subtypes but doesn't show the totals of how many of each multi sub-type account there are (how many accounts with 2 subtypes vs. 3 subtypes and so on). I'm looking for a wholly SQL way to view how many of each grouping of account types. There are a LOT of accounts in the table so pulling it manually isn't really an option. Is there a way I can get a count of each of the amount of sub-type groupings?

| account | Sub-Type | Single_V_Multi |
|---------|--------- | -------------- |
|123456789|123456789 | Multi          |
|123456789|123456790 | Multi          | 
|123456789|123456791 | Multi          |
|123456792|123456792 | Single         |
|123456793|123456793 | Multi          |
|123456793|123456794 | Multi          |
|123456795|123456795 | Single         |
|123456796|123456796 | Single         |
|123456797|123456797 | Single         |
|123456798|123456798 | Single         |
|123456799|123456799 | Multi          |
|123456799|123456800 | Multi          |
|123456799|123456801 | Multi          |
|123456799|123456802 | Multi          |

From this example I'd be looking to get separate counts of the Account column based on the number of unique Sub-Type. What I've done so far is a query that groups the Sub-Types:

SELECT account, COUNT(DISTINCT(Sub-Type)) as BAN_SUB_COUNT
FROM Table

Which give the output:

| account | BAN_SUB_COUNT |
| ------- | ------------- |
|123456789| 3             |
|123456792| 1             |
|123456793| 2             |
|123456795| 1             |
|123456796| 1             |
|123456797| 1             |
|123456798| 1             |
|123456799| 4             |

What I need from this is a way to get a separate count of accounts for each of the distinct BAN_SUB_COUNT entries. Ideally it would be along the lines of:

| BAN_SUB_COUNT |count of Accounts|
| ------------- | --------------- |
| 1             | 5               |
| 2             | 1               |
| 3             | 1               |
| 4             | 1               |

Sorry for any confusion and I hope I'm explaining myself better here!

CodePudding user response：

You just need to wrap your query with another one:

select ban_sub_count, count(distinct account) as count_of_accounts
from (
  SELECT account, COUNT(DISTINCT Sub-Type ) as BAN_SUB_COUNT
  FROM Table
  group by account
)z
group by ban_sub_count

Output:

BAN_SUB_COUNT	count of Accounts
1	5
2	1
3	1
4	1

CodePudding user response：

I try to answer your question:

select a2.*,a.`count_sub_type`
FROM (
    select count(`sub-type`) as count_sub_type,`sub-type` from account group by `sub-type`
) a
left join account a2 on a2.`sub-type` = a.`sub-type`;

output :

|account |sub-type|single_v_multi|count_sub_type|
|--------|--------|--------------|--------------|
|account6|type1   |multiview     |3             |
|account5|type1   |single        |3             |
|account1|type1   |single        |3             |
|account4|type2   |single        |2             |
|account2|type2   |single        |2             |
|account6|type3   |single        |2             |
|account3|type3   |single        |2             |

Best regards,