Using count with crossapply

Transact-SQL
1

I need to get a count of rows, after I use cross apply to expand a delimited field. The end result is I want to run this over my entire table(not very big) and get a count of report id's for a host.
For example, to keep it fairly simple I have a hostname that is associated to a field called reports. Reports is actually a comma delimited list of report id's. So in this table I have 1 host record and a reports field. like this

hostname        |     reports                  |
host1           |     rpt1, rpt2,rpt3,rpt4|
host2           |     rpt6,rpt8                |
 etc..

I'm using cross apply to expand the reports field out and give me multiple rows like below.
basic query

select hostname, report
from tableA as a, cross apply DelimitedSplit(report,',') as tableB
where a.status = 'active'
and a.hostname = 'host1'

output:

hostname     | Report
host1        | rpt1
host1        | rpt2
host1        | rpt3
etc..

then I wanted to get a count, so I tried adding a count

select hostname, report,count(report) as rpt_count
from tableA as a, cross apply DelimitedSplit(report,',') as tableB
where a.status = 'active'
and a.hostname = 'host1'

this only added a 1 next to each row, but I want 1 row and account of reports like this

hostname       |  report
host1          | 15

I then tried to make this a subquery with a count on the outside.

select count(*) as rpt_count (
select hostname, report
from tableA as a, cross apply DelimitedSplit(report,',') as tableB
where a.status = 'active'
and a.hostname = 'host1'
)
as work_table
where hostname = 'host1'

but the count is way off. Any ideas? I'd like to be able to do this without specifying the host, so I could run it over my entire table. Thanks in advance.

2

Would you please post the sample data as CREATE TABLE and INSERT statement(s) rather than just a picture of data. That way we can immediately start writing SQL for you.

3

Please provide consumable test data in future:

CREATE TABLE #t
(
	hostname varchar(20) NOT NULL PRIMARY KEY
	,reports varchar(255) NOT NULL
);
INSERT INTO #t
VALUES ('host1','rpt1, rpt2,rpt3,rpt4')
	,('host2','rpt6,rpt8');

Using the following string splitter function:

SELECT T.hostname, LTRIM(RTRIM(X.Item)) AS report
	,COUNT(*) OVER (PARTITION BY T.hostname) AS rpt_count
FROM #t T
	CROSS APPLY dbo.DelimitedSplit8K(T.reports, ',') X
4

The code sample you provided is invalid - you have a comma before the cross apply which is invalid syntax. I have to assume that this is not the full query and that you have removed portions of the code - and that may be where the actual issue is occurring.

With that said - here is a basic outline:

Declare @testData Table (hostname varchar(20), reports varchar(100));
 Insert Into @testData (hostname, reports)
 Values ('host1', 'rpt1,rpt2,rpt3,rpt4')
      , ('host2', 'rpt6,rpt8');

 Select td.hostname
      , TotalReports = count(*)
   From @testData               td
  Cross Apply string_split(td.reports, ',') As b
  Group By
        td.hostname;

This works as expected - but if you really do have additional code where the 'join' is done using the old-style comma, then my guess is that you have accidentally created a cartesian product which results in incorrect totals.

5 

SELECT
    t.hostname, 
    ds.Item AS report,
    COUNT(*) OVER() AS total_report_count
FROM #t t
CROSS APPLY dbo.DelimitedSplit8K(reports, ',') ds
6

thank you for the solutions guys. I will give them a test. I had to step away and wasn't able to upload sample data, I'll make sure to do that next time.