Forum Discussion

sk2019's avatar
sk2019
Copper Contributor
Apr 03, 2019

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.

 

countryCustomerAmountAmount of Dell + hpAmount of hp+xerox
AusDell100  
Aushp200  
AusXerox140  
Indhp234  
Inddell34  
IndXerox523  
  • frederic MICHALAK's avatar
    frederic MICHALAK
    Copper Contributor
    Apr 12, 2019

    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.
     
     

     

Resources