Forum Discussion
sql server SUM based on criteria on the column
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. country Customer Amount Amount of...
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)
(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
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
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.
