In MySQL, can we use the COUNT and SUM value to calculate a value in another output column using a single SELECT?

Question

Lets say we have something like:

SELECT COUNT(IF(Column1 = 1, NULL, 1)) AS "A", SUM(Column2) AS "B" FROM MyDatabase;

On the same query, can we add a column that would be A * B for example with one single SELECT overall?

GMB · Accepted Answer · 2020-02-05T11:25:46.413

1

You need to either repeat the expression, or turn it to a subquery and do the computation in the outer query. Your expression is not that complicated (and can be simplified, as shown below), so I would go for the first option here:

SELECT 
    SUM(Column1 = 1) a, 
    SUM(Column2) b,
    SUM(Column1 = 1) * SUM(Column2) c
FROM MyDatabase;

edited Feb 05 '20 at 11:25

answered Feb 05 '20 at 11:13

GMB

216,147
25
84
135

I see....sneaky... Seems like the calculated column gave me A * B but without considering the IF(...). The count should not be considered under some situations... – CelestialEX Feb 05 '20 at 11:20

score 0 · Answer 2 · answered Feb 05 '20 at 11:31

Assuming that column1 only takes on the values of 0/1, you can do:

sum(column1 * column2)

If it can take on other values, then:

sum( (column1 = 1) * column2 )

Both of these use the MySQL extension that booleans are handled as numbers, with "1" for true and "0" for false.

In MySQL, can we use the COUNT and SUM value to calculate a value in another output column using a single SELECT?

2 Answers2