sql query to sum data based on criteria

Question

I have my table data sums as follows

---------------------
Building   | Area m2
---------------------
|Dante 12  |   10
|Dante 10  |    5
|Dante 9   |    2
|Crandley  |   20
|Bence     |   30

I want to sum Building Area but buildings like '%dante%' I want to combine into sum "Dante" like below:

-------------------
Building | Area m2
-------------------
Dante    |  17
Crandley |  20
Bence    |  30

What version of SQL you are using, as an exact answer may depend on this. — Tim Biegeleisen, Aug 16 '16 at 16:00
I assume that you don't want to fix this just for "Dante", and that you can actually have more combination of numbers, i.e: "Crandley 8"? — Lamak, Aug 16 '16 at 16:02

score 2 · Answer 1 · edited May 23 '17 at 12:08

Group by with case will do the trick for sample data given,like of this type can use index as well

Select 
case when building like 'dante%' then 'Dante' else building end,
sum([area m2])
from
table
group by 
case when building like 'dante%' then 'Dante' else building end

if there are numbers for other columns,you can strip out the numbers first and do the rest of stuff like below

;with cte
as
(select REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (building, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '') as bulding,aream2
 from #temp
)
select building,sum(aream2)
from
cte 
group by 
building

References:
Remove numbers found in string column

thank you very much! it is correct answer, it worked! – Simona Sopagaite Aug 16 '16 at 16:07 — Simona Sopagaite, Aug 16 '16 at 16:07

score 0 · Answer 2 · answered Aug 16 '16 at 16:14

Works only if numeric value is to be removed

Numeric value is to be replaced (Nested replace function can go upto 32 level deep) and group by column.

SELECT x.Building, SUM(x.AreaM2) AS [Area m2]
FROM (
    SELECT 
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE (Building, '0', '')
        ,'1', '')
        ,'2', '')
        ,'3', '')
        ,'4', '')
        ,'5', '')
        ,'6', '')
        ,'7', '')
        ,'8', '')
        ,'9', '') [Building], AreaM2 FROM Buildings) AS x
GROUP BY x.Building

sql query to sum data based on criteria

2 Answers2