MySQL Forums
Forum List  »  General

Re: How can I get a sum from specific fields in where clause?
Posted by: Peter Brawley
Date: April 08, 2022 12:11PM

A big part of improving from bad to good programming is learning how to write the requirement so clearly, a 12-year-old novice understands it instantly.

First, it's a design error to define a column as text when arithmetic will be done on its contents. It needs to be int or decimal.

Then, your statement of the requirement ...
...

Quote

WHERE srvid=1 AND if(QID <> GivenQID, TRUE, if(TheQuery, TRUE, FALSE)) = TRUE
TheQuery: SUM(Answer) of any specific MX >= GivenValue

... entails that the query know sum(`answer`) before it runs. Obviously not possible.

The requirement appears to be at least a 2-step ...

1 Find rows for which sum(`answer`) >= givenvalue. For this, you'll need to specify exactly how to define which rows are to be thus Grouped By.

2 Then apply the other criteria referenced above to those rows.

Here's a little toy table we could use for working this up ...

drop table if exists t;
create table t (srvid int, qid int, mx int, ma int, answer int );
insert into t values
(1,1,1,1,1),(1,2,2,2,2),(1,2,3,3,4),(2,3,4,4,4),(3,4,5,5,5);



Edited 1 time(s). Last edit at 04/08/2022 12:15PM by Peter Brawley.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.