CODEWITHSUNDEEP
sqlsum
Simona Sopagaite
Simona Sopagaite

Reputation: 11

sql query to sum data based on criteria

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

Upvotes: 1

Views: 731

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 2

sawbeanraz
sawbeanraz

Reputation: 177

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

Upvotes: 0

Related Questions