SQL Returning count of common rows in a column with case when-CodePudding

I have this table where I can return a list of rows for a specific date range. The row it is pulling from has a more data in it than I need so I've reduced that down using a case when

SELECT case when w.[description] like '%Provisioning%' then left (w.[description],charindex('Provisioning',w.[description])-2)
                                         else '' end as [Application]
FROM [Table 1] as w
left join [Table2] as i on i.[id] = w.[owner]
where [owner_name] in ('Specific_Owner')
and  (convert(datetime,dateadd(s,w.[created]/1000,'1970-01-01'),110) BETWEEN Convert(datetime, '2022-05-09' ) AND Convert(datetime, '2022-06-01' ))
and w.[description] not like '%Approve%'

Which gives an output as below:

Application
Application_A
Application_B
Application_A
Application_A
Application_A
Application_C
Application_C

I would like to return a count of each application

So something like

Application	Counts
Application_A	4
Application_B	1
Application_C	2

Struggling a bit with this one! Any help would be appreciated :)

CodePudding user response：

You add the group_by w.[description] and count(*) in select to get the exact result :

SELECT case when w.[description] like '%Provisioning%' then left (w.[description],charindex('Provisioning',w.[description])-2)
                                     else '' end as [Application],count(*)
FROM [Table 1] as w
left join [Table2] as i on i.[id] = w.[owner]
where [owner_name] in ('Specific_Owner')
and  (convert(datetime,dateadd(s,w.[created]/1000,'1970-01-01'),110) BETWEEN Convert(datetime, '2022-05-09' ) AND Convert(datetime, '2022-06-01' ))
and w.[description] not like '%Approve%'
group by w.[description];

CodePudding user response：

You can use the case statement in group by like below and do a count(*)

SELECT case when w.[description] like '%Provisioning%' then left (w.[description],charindex('Provisioning',w.[description])-2)
                                         else '' end as [Application], count(*)
FROM [Table 1] as w
left join [Table2] as i on i.[id] = w.[owner]
where [owner_name] in ('Specific_Owner')
and  (convert(datetime,dateadd(s,w.[created]/1000,'1970-01-01'),110) BETWEEN Convert(datetime, '2022-05-09' ) AND Convert(datetime, '2022-06-01' ))
and w.[description] not like '%Approve%'
group by case when w.[description] like '%Provisioning%' then left (w.[description],charindex('Provisioning',w.[description])-2)
                                         else '' end