MySQL:根据两个标准对多列中的整数值进行计数和总和

Question

我需要做我做过的同样的事情here .这次我想要一个基于两个标准的结果。所以我有一张这样的 table 。

images     |     videos      |      reviews        |       publisherId
----------------------------------------------------------------------------
0          |     2           |       1             |          91
4          |     5           |       0             |          91
11         |     1           |       4             |          12
0          |     1           |       7             |          12
2          |     1           |       9             |          12

我需要编写一个查询来计算每列中不等于 0 的行数，并给出列中数字的实际总和并根据发布者编号。所以结果将如下所示。

Columns     |     Counts(91)   |    Sum(91)  |    Counts(12)   |    Sum(12)
----------------------------------------------------------------------------
images      |     1            |    4        |     2           |    13
videos      |     2            |    7        |     3           |    3
reviews     |     1            |    1        |     3           |    20

查询给出来自 answer 的一个 publisherId 的结果我的另一个问题是这个。

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;

如何实现这个结果？

Answer 1

这根本不需要加入。这只是更多的条件聚合。我将展示发布者 91 的逻辑。您可以自己为 12 添加两列:

SELECT 'images' as `columns`,
       SUM(images <> 0 and publisherid = 91) as counts_91,
       SUM(case when publisherid = 91 then images else 0 end) as sum_91
FROM dpsreport
UNION ALL
SELECT 'videos',
       SUM(videos <> 0 and publisherid = 91) as counts_91,
       SUM(case when publisherid = 91 then videos else 0 end) as sum_91
FROM dpsreport
UNION ALL
SELECT 'reviews',
       SUM(reviews <> 0 and publisherid = 91) as counts_91,
       SUM(case when publisherid = 91 then reviews else 0 end) as sum_91
FROM dpsreport;

随着您练习逻辑，您应该会更容易弄清楚如何自己解决这些问题。