This can be done using a simple aggregation query :

select 'Tours ' + SubVenue as Grp, sum(Tours) as [Total Tours]
from Tour_Details
group by SubVenue

When you do this SELECT 'Tours ' + SubVenue AS Grp FROM Tour_Details your Grp equals Tours SP1 and you try to join it to SubVenue which equals SP1 . Tours SP1 <> SP1 . You need to have the equals values in columns which you use in join.

And you can do it simpler.

SELECT
    'Tours ' +SubVenue AS Grp, 
    SUM(Tours) AS Total
FROM Tour_Details
GROUP BY 'Tours ' +SubVenue

I think you're over-thinking your problem, you just need a simple aggregation:

select Grp, sum(tours) Total_Tours
from Tour_Details
cross apply (values(Concat('Tours ', SubVenue))) g (Grp)
group by Grp;

I think your SQL is failing because you are concatenating the string 'Tour ' with the contents of the subvenue field then using that concatenated value from the query result to LEFT JOIN back to the Tour Details table field subvenue. The join is returning the cartesian product (if it's still called that, sorry, I'm old...) along with the sum results.

I created a table using VARCHAR(100) for all the string fields and INTEGER for all the number fields. I'm not sure what RDBMS you're using but my dev database is postgres so the syntax is slightly different (namely, the string concatenation operator is '||' rather than '+' but I think the SQL you're looking for is along the lines of:

select 'Tours '||subvenue,sum(tours) from tourdetails group by subvenue;

I created a view with it like this:

create view subvenuesummary as (
select 'Tours '||subvenue,sum(tours) from tourdetails group by subvenue);

And a table like this:

create table sumsubvenuetable as (
select 'Tours '||subvenue,sum(tours) from tourdetails group by subvenue);

Both work fine and I'm sure the syntax would be similar enough in your database.