MySQL：获取多个列中的整数值的计数和总和 [英] MySQL: Getting count and sum of integer values in multiple columns

问题描述

好的，所以我的要求是相当简单。我有一个这样的表。

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

我需要编写一个查询，计算每列中不等于 0 ，也给出了列中数字的实际和。所以结果看起来像这样。

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

这是SQL的直接可能吗？我不想在应用程序级别上做总和。到目前为止，我写了这个查询，它工作正常，只是没有总和部分。

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;

请注意，查询中的 SUM

推荐答案

我认为这是你想要的： / p>

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;

注意：

• 不等于的SQL标准为<> ，而不是！= ）。


• 使用 UNION ALL 而不是 UNION （在这种情况下可以忽略不计）。

• 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).

这篇关于MySQL：获取多个列中的整数值的计数和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！