Double count query in the same table

My original syntax:

select count (*) as Total, description
from  ADSAccount
group by Description

Results:

Total   Description
--------------------
 20     CloudRoom
100     User
200     Cloud
800     AD

I'm trying to add a count separation of the "AD" accounts.

I need to split count 800 AD accounts with the following where clause: where SAMAccountName like 'DOM%' and description = 'AD'

Tried the following but its not working:

select count (*) as total, description 
from ADSAccount 
left join 
    (select count (*) as DOM 
     from ADSAccount 
     where SAMAccountName like 'DOM%' 
       and description = 'AD')
group by Description

Expected Result Results:

Total   Description
--------------------
 20     CloudRoom
100     User
200     Cloud
700     AD
100     AD-DOM

Thanks for any advice.

CodePudding user response：

Try something like:

select count (*) as total
,case when SAMAccountName like 'DOM%' and description = 'AD' then 'DOM'
 else description end as description1
from ADSAccount 
group by description1

CodePudding user response：

you can use case when if your SQL doesn't support if statement

with main as (

select
case when SAMAccountName like 'DOM%' and description = 'AD' then 'DOM'
 else description end as new_description
from ADSAccount
)
select 
new_description as description,
count(*) as total
from main
group by new_description

with main as (

select
if(SAMAccountName like 'DOM%' and description = 'AD','DOM',description) as new_description
from ADSAccount
)
select 
new_description as description,
count(*) as total
from main
group by new_description