Finding SUM of different columns with different WHERE conditions-CodePudding

I have the following table called designerindices

I want to do the sum and the pseudo code as below

SUM(duration1) WHERE designer1 = X AND designerHistSrNumber = 16 SUM(duration2) WHERE designer2 = X AND designerHistSrNumber = 16 SUM(duration3) WHERE designer3 = X AND designerHistSrNumber = 16 SUM(duration4) WHERE designer 4 = X AND designerHistSrNumber = 16

To do that I wrote the following 4 separate queries

SELECT SUM(duration1) as sumdur1 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X" This should output 10

SELECT SUM(duration2) as sumdur2 FROM designerindices WHERE designerHistSrNumber = 16 AND designer2 = "X" This should output 2.4

SELECT SUM(duration3) as sumdur3 FROM designerindices WHERE designerHistSrNumber = 16 AND designer3 = "X" This should output 5

SELECT SUM(duration4) as sumdur4 FROM designerindices WHERE designerHistSrNumber = 16 AND designer4 = "X" This should output 1.1

I have to execute the above queries 4 times. The finally add sumdur1 sumdur2 sumdur3 sumdur4. The total should be 18.5

Is there any easy and direct way to do this instead of doing the above way?

CodePudding user response：

You can wrap all your 4 queries in seperate Select clause and have addition in that, like

Select (
(SELECT SUM(duration1) as sumdur1 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
 
(SELECT SUM(duration2) as sumdur2 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
 
(SELECT SUM(duration3) as sumdur3 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
 
(SELECT SUM(duration4) as sumdur4 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
) from Dual;

CodePudding user response：

What you want here generally is called conditional aggregation:

SELECT
    SUM(CASE WHEN designer1 = 'X' THEN duration1 ELSE 0 END) AS dur1,
    SUM(CASE WHEN designer2 = 'X' THEN duration2 ELSE 0 END) AS dur2,
    SUM(CASE WHEN designer3 = 'X' THEN duration3 ELSE 0 END) AS dur3,
    SUM(CASE WHEN designer4 = 'X' THEN duration4 ELSE 0 END) AS dur4
FROM yourTable
WHERE
    designerHistSrNumber = 16

CodePudding user response：

Why not just select the individual SUMS:

SELECT
  SUM(duration),
  SUM(duration2),
  SUM(duration3),
  SUM(duration4)
FROM
  designerindices
WHERE
  designerHistSrNumber=16 AND designer1="X"

This will sum the individual columns, but only for rows that match the WHERE clause. Since you want the same WHERE clause for each SUM(), you can just specify it once.