CODEWITHSUNDEEP
sqlsql-server
Roxx
Roxx

Reputation: 3996

Sum values of two columns row in SQL

Below is the query I have prepared to fetch the details from the database.

select vUI.ArticleID , vUCS.NumInstalled,vUCS.NumPresent, vUCS.NumPending, vUCS.NumFailed, vUCS.NumNotApplicable , vUCS.NumMissing, vUCS.NumUnknown , vUCS.NumTotal
from v_Update_DeploymentSummary_Live vUCS 
inner join v_UpdateInfo vUI
    on vUCS.CI_ID=vUI.CI_ID
where vUCS.CollectionID='RA00686' --or  vUCS.CollectionID='RA00785'

There are a couple of things I tried to achieve but getting hard time.

Below is the output data.

enter image description here

I am hoping that screenshot should be visible. but in case if its missing I would like to add values of vUCS.NumInstalled and vUCS.NumPresent as both are showing data for success.

How can I add the value of two columns in a row?

My query is little bit slow due to vUCS.CollectionID='RA00686' or vUCS.CollectionID='RA00785', I am trying to change it as it is taking more than 3 minutes to run.

Current Output

ArticleID   NumInstalled    NumPresent  NumPending
4484107         2               16           2
4519998         0              0             0
4521860        7573           7738          13

Expected Output

ArticleID   NumInstalled    NumPending
4484107         18            2
4519998         0             0
4521860        15311          13

Upvotes: 0

Views: 105

Answers (1)

DineshDB
DineshDB

Reputation: 6193

Try the below:

select vUI.ArticleID , 
    ISNULL(vUCS.NumInstalled,0)+ISNULL(vUCS.NumPresent,0) AS NumInstalled,
    vUCS.NumPending
from v_Update_DeploymentSummary_Live vUCS 
inner join v_UpdateInfo vUI
    on vUCS.CI_ID=vUI.CI_ID
where vUCS.CollectionID='RA00686'

And you have to create an Index for the vUCS.CollectionID column to increase the query performance.

CREATE NONCLUSTERED INDEX NCX_v_Update_DeploymentSummary_Live_Indx1 ON v_Update_DeploymentSummary_Live(CollectionID)

Upvotes: 1

Related Questions