mysql中查询多个字段的总和_MySQL：获取多个列中的整数值的计数和总和

Ok so my requirement is fairly simple. I have a table like so.

images | videos | reviews

--------------------------------------------------

0 | 2 | 1

4 | 5 | 0

11 | 1 | 4

0 | 1 | 7

I need to write a query that counts the rows in each column that is not equal to 0, and also that gives the actual sum of the numbers in the columns. So the result will look something like this.

Columns | Counts | Sum

----------------------------------------------------

images | 2 | 15

videos | 4 | 9

reviews | 3 | 13

Is this directly possible with SQL? I don't want to do the sum on the application level. So far I have written this query and it works fine, just without the sum part.

SELECT 'images', SUM(images != 0)

FROM dpsreport where publisherId = 91

UNION

SELECT 'videos', SUM(videos != 0)

FROM dpsreport where publisherId = 91

UNION

SELECT 'reviews', SUM(reviews != 0)

FROM dpsreport where publisherId = 91;

Note that SUM in the query gives me the count of the rows, not the actual sum, as in mathematical addition.

解决方案

I think this does what you want:

SELECT 'images' as `columns`, SUM(images <> 0) as counts, SUM(images) as `sum`

FROM dpsreport where publisherId = 91

UNION ALL

SELECT 'videos', SUM(videos <> 0), SUM(videos)

FROM dpsreport where publisherId = 91

UNION ALL

SELECT 'reviews', SUM(reviews <> 0), SUM(reviews)

FROM dpsreport where publisherId = 91;

Notes:

The SQL standard for not equals is <> rather than != (although many databases support both).

Use UNION ALL rather than UNION, to avoid the overhead of removing duplicates (which is negligible in this case).