sql server SUM based on criteria on the column

%3CLINGO-SUB%20id%3D%22lingo-sub-393567%22%20slang%3D%22en-US%22%3Esql%20server%20SUM%20based%20on%20criteria%20on%20the%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393567%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20add%202%20more%20columns%20to%20my%20table%20that%20is%20based%20on%20criteria%20on%20one%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebelow%20is%20my%20requirement%2C%20add%20last%202%20columns%20based%20on%20Customer%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Ecountry%3C%2FTD%3E%3CTD%3ECustomer%3C%2FTD%3E%3CTD%3EAmount%3C%2FTD%3E%3CTD%3EAmount%20of%20Dell%20%2B%20hp%3C%2FTD%3E%3CTD%3EAmount%20of%20hp%2Bxerox%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAus%3C%2FTD%3E%3CTD%3EDell%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAus%3C%2FTD%3E%3CTD%3Ehp%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAus%3C%2FTD%3E%3CTD%3EXerox%3C%2FTD%3E%3CTD%3E140%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EInd%3C%2FTD%3E%3CTD%3Ehp%3C%2FTD%3E%3CTD%3E234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EInd%3C%2FTD%3E%3CTD%3Edell%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EInd%3C%2FTD%3E%3CTD%3EXerox%3C%2FTD%3E%3CTD%3E523%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-445756%22%20slang%3D%22en-US%22%3ERe%3A%20sql%20server%20SUM%20based%20on%20criteria%20on%20the%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-445756%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313143%22%20target%3D%22_blank%22%3E%40sk2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3EJust%20to%20be%20clear%20about%20the%20real%20technical%20need%20%3A%20Can%20you%20confirm%20that%20your%20two%20additional%20columns%20are%20computed%20from%20the%20initial%20Amount%20value%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that's%20the%20case%2C%20adding%20two%20additional%20columns%20is%20just%20the%20wrong%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20thing%20I%20though%20was%20an%20SSAS%20Cube.%3C%2FP%3E%3CP%3EIf%20not%20possible%2C%20consider%20a%20calculated%20view%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%3ECreate%20Table%20MyTable%3CBR%20%2F%3E(country%20varchar(20)%2C%3CBR%20%2F%3E%26nbsp%3BCustomer%20varchar(20)%2C%3CBR%20%2F%3E%26nbsp%3BAmount%20int)%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Aus'%2C%20'Dell'%2C%20100)%3CBR%20%2F%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Aus'%2C%20'hp'%2C%20200)%3CBR%20%2F%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Aus'%2C%20'Xerox'%2C%20140)%3CBR%20%2F%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Ind'%2C%20'hp'%2C%20234)%3CBR%20%2F%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Ind'%2C%20'dell'%2C%2034)%3CBR%20%2F%3E%26nbsp%3BInsert%20into%20MyTable%20values%20('Ind'%2C%20'Xerox'%2C%20523)%3CBR%20%2F%3EGo%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3ECreate%20View%20MyCube%3CBR%20%2F%3Eas%3CBR%20%2F%3ESelect%26nbsp%3Bcountry%2C%20Customer%2C%20Amount%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B(Select%20SUM(Amount)%20from%20MyTable%20T2%20where%20(T2.country%20%3D%20T1.country)%20And%20(Customer%20in%20('Dell'%2C%20'hp')))%20as%20'Amount%20of%20Dell%20%2B%20hp'%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B(Select%20SUM(Amount)%20from%20MyTable%20T2%20where%20(T2.country%20%3D%20T1.country)%20And%20(Customer%20in%20('xerox'%2C%20'hp')))%20as%20'Amount%20of%20hp%20%2B%20xerox'%3CBR%20%2F%3Efrom%20MyTable%20T1%20%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ENote%20that%20a%20Clustured%20Columnstore%20Index%20may%20be%20valuable%20depending%20the%20data%20volume.%3C%2FDIV%3E%3CDIV%3EHope%20this%20helps.%3C%2FDIV%3E%3CDIV%3EFred.M.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I want to add 2 more columns to my table that is based on criteria on one column.

 

below is my requirement, add last 2 columns based on Customer column.

 

countryCustomerAmountAmount of Dell + hpAmount of hp+xerox
AusDell100  
Aushp200  
AusXerox140  
Indhp234  
Inddell34  
IndXerox523  
1 Reply

@sk2019 

Hi, 
Just to be clear about the real technical need : Can you confirm that your two additional columns are computed from the initial Amount value ? 

If that's the case, adding two additional columns is just the wrong way.

 

The first thing I though was an SSAS Cube.

If not possible, consider a calculated view : 

 

Create Table MyTable
(country varchar(20),
 Customer varchar(20),
 Amount int)
 Insert into MyTable values ('Aus', 'Dell', 100)
 Insert into MyTable values ('Aus', 'hp', 200)
 Insert into MyTable values ('Aus', 'Xerox', 140)
 Insert into MyTable values ('Ind', 'hp', 234)
 Insert into MyTable values ('Ind', 'dell', 34)
 Insert into MyTable values ('Ind', 'Xerox', 523)
Go
Create View MyCube
as
Select country, Customer, Amount,
  (Select SUM(Amount) from MyTable T2 where (T2.country = T1.country) And (Customer in ('Dell', 'hp'))) as 'Amount of Dell + hp',
  (Select SUM(Amount) from MyTable T2 where (T2.country = T1.country) And (Customer in ('xerox', 'hp'))) as 'Amount of hp + xerox'
from MyTable T1
 
Note that a Clustured Columnstore Index may be valuable depending the data volume.
Hope this helps.
Fred.M.