SQL query, adding up values which contain string

Question

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

Is there any way that you could change the string to say simply '6' instead of '6 per hour'? What you currently have would require string parsing. — earl3s, Dec 20 '17 at 19:43
SUM() will work for number values, you have text in there. Or what you are showing in your picture is an application view? So we have no way to know what the DB actually contains. FYI for database questions, add this info: which DB you are using, table definition, show data actually in the database, not application processed. — Nic3500, Dec 20 '17 at 19:50
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. — , Dec 20 '17 at 20:55

score 1 · Answer 1 · answered Dec 20 '17 at 20:36

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

Assuming the ' per hour' piece is consistent, I think this is a great answer! — TLaV, Dec 20 '17 at 20:43

score 1 · Answer 2 · answered Dec 20 '17 at 20:41

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

score 0 · Answer 3 · answered Dec 20 '17 at 20:23

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.

SQL query, adding up values which contain string

3 Answers3