I have this table:
I want to workout how many bus journeys per hour are operated by Bond Brothers and the expected results is 7 per hour
My questions is how do I add up values that contain strings?
SELECT Operator, SUM(Frequency) AS “Total Frequency”
FROM Bus Routes
I don't think this would work would it?
Thanks
for that
declare @table as table (
group_id int,
content varchar(100)
)
insert into @table values (2, '2 per hour')
insert into @table values (1, '1 per hour')
insert into @table values (1, '6 per hour')
insert into @table values (1, '')
insert into @table values (3, '9 per hour')
insert into @table values (3, null)
select * from @table
do
select t.group_id, SUM(t.content) content_sum
from (select group_id, convert(int, REPLACE(content, ' per hour', ''))
content from @table) t
group by t.group_id
Borrowing from this How to get the numeric part from a string using T-SQL?, you could do something like this (note the cast to an integer):
SELECT Operator, SUM(CAST(left(Frequency, patindex('%[^0-9]%', Frequency+'.') - 1) AS INT))
FROM
(VALUES
('2 per hour','Ok Travel')
,('6 per hour','Diamond Buses')
,('1 per hour','Bond Brothers')
,('6 per hour','Bond Brothers')
,('','Diamond Buses')
) BusRulles(Frequency,Operator)
GROUP BY Operator
WITH CTE AS(
SELECT ts.Destination ,
CONVERT(INT,REPLACE(ts.Frequency, ' per hour','')) AS Frequency ,
ts.Operator ,
ts.[Route Number] ,
ts.Start
FROM dbo.temp_Sqlcheck ts
WHERE ts.Frequency IS NOT NULL OR ts.Frequency <>''
)
SELECT CTE.Operator, SUM(CTE.Frequency) AS Frequency from CTE
GROUP BY CTE.Operator
This will not count NULL or Blank values in Frequency.
