Finding SUM of different columns in MySQL with different WHERE conditions

Question

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?

This may answer your question :stackoverflow.com/questions/11033340/… — greetal
– greetal, Commented Nov 16, 2021 at 4:01

Navneet Rabadiya · Accepted Answer · 2021-11-16 04:06:21Z

2

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;

answered Nov 16, 2021 at 4:06

Navneet Rabadiya

2201 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anu Over a year ago

This works well too. But if use multiple select cases, will it be slow when comes to large tables?

Tim Biegeleisen Over a year ago

@Anu The downside of this approach is that it requires four separate full scans of the designerindices table, whereas using a single select with conditional aggregation only requires one scan.

Tim Biegeleisen · Accepted Answer · 2021-11-16 04:08:58Z

1

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

answered Nov 16, 2021 at 4:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

7 Comments

Anu Over a year ago

seems like it is not doing SUM. When I run this query, it is just getting the first value of each duration column only. Eg: dur1 showing 10, dur2 showing 2.4, dur3 showing 5 and dur4 showing 1.1.

kmoser Over a year ago

@Anu According to your sample data, there is only one record where designer1='X' and designerHistSrNumber=16. Same for the other SUMs: there is only one matching record for each of those conditions. That explains why dur1 = 10, dur2 = 2.4, etc.

Anu Over a year ago

@kmoser Sorry my bad. You are right. Your suggestion is correct.

Tim Biegeleisen Over a year ago

Just add together the four CASE expressions in my answer. If you find that too ugly, then you could subquery my answer and then just sum the aliases.

kmoser Over a year ago

@Anu Unless your DB is enormous and/or poorly indexed, I suspect the performance differences between the two methods will be negligible. But don't take my word for it: perform some tests and let us know the results!

|

kmoser · Accepted Answer · 2021-11-16 04:10:15Z

0

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.

edited Nov 16, 2021 at 4:10

answered Nov 16, 2021 at 4:08

kmoser

9,3693 gold badges28 silver badges45 bronze badges

5 Comments

Tim Biegeleisen Over a year ago

...because the restriction isn't always on designer1, it is also on designer2, designer3, etc.

kmoser Over a year ago

I was basing my query on OP's individual queries, all of which are dependent on designer1. So perhaps the original post needs to be clarified.

Anu Over a year ago

@kmoser Sorry that was a typo mistake from me. designer must vary for each query

Anu Over a year ago

I just corrected it

Tim Biegeleisen Over a year ago

@kmoser Please read the above 2 OP comments, which confirm my initial comment under your answer.

Collectives™ on Stack Overflow

Finding SUM of different columns in MySQL with different WHERE conditions

3 Answers 3

2 Comments

7 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related