MySQL结合了COUNT，MAX和SUM [英] MySQL combining COUNT, MAX and SUM

问题描述

在MySQL中，我希望有一个额外的列显示特定列的值的总和。然而，我想总结的数字来自一个子查询，而不是存储在一个单独的表中，如下所示：

  （SELECT a.ID，MAX（a.COUNT_ID）AS MAX_COUNT FROM 
（SELECT ID，COUNT（*）AS COUNT_ID 
 FROM my_table 
 GROUP BY COL1，COL2）a 
 GROUP BY COL1，COL2）b 
  

这会输出如下内容：

  ID MAX_COUNT 
 ABC 1 
 DEF 2 
 GHI 3 
  

现在，我需要一个额外的列来显示MAX_COUNT的总和，就像这样（对所有行重复）：

  ID MAX_COUNT SUM_MAX_COUNT 
 ABC 1 6 
 DEF 2 6 
 GHI 3 6 
  

实际目标实际上是显示总共MAX_COUNT的百分比MAX_COUNT，即1/6，2/6和3/6。
我该怎么做？我已经尝试做一个 CROSS JOIN 但它不起作用：

  SELECT * FROM 
（（SELECT a.ID，MAX（a.COUNT_ID）AS MAX_COUNT FROM 
（SELECT ID，COUNT（*）AS COUNT_ID 
 FROM my_table 
 GROUP BY COL1，COL2）a 
 GROUP BY COL1，COL2）b 
 CROSS JOIN（SELECT SUM（b.MAX_COUNT））AS c 
  

错误：未知的表'b'

---

/ strong>

示例表：

  CREATE TABLE TABLE1（
 COL1 varchar（255），
 COL2 varchar（255），
 DAY int，
 HOUR int）; 
 
 INSERT INTO TABLE1 VALUES 
 （'X'，'Y'，1,12），
（'X'，'Y'，1,13），
（'X'，'Y'，1,13）， 
（'A'，'B'，2,19），
（'X'，'B'，3,13），
（'X'，'B'，3 ，13）; 
  

现在我想要对于COL1和COL2的每个组合，每小时在此表格中显示以下行：

  SELECT COL1，COL2，HOUR，COUNT（*）AS COUNT_LINES 
 FROM TABLE1 
 GROUP BY DAY，HOUR，COL1，COL2 ; 
  

其中输出：

  COL1 COL2 HOUR COUNT_LINES 
 XY 12 1 
 XY 13 2 
 AB 19 1 
 XB 13 2 
  

现在我想要为COL1和COL2的每个组合，COUNT_LINES的最大值，所以我在子查询中使用上面的查询：

  SELECT a.COL1，a.COL2，MAX（a.COUNT_LINES）
 FROM 
（SELECT COL1，COL2，HOUR ，COUNT（*）AS COUNT_LINES 
 FROM TABLE1 
 GROUP BY DAY，HOUR，COL1，COL2）a 
 GROUP BY COL1，COL2; 
  

其中输出：

  COL1 COL2 MAX（COUNT_LINES）
 AB 1 
 XB 2 
 XY 2 
  

现在，在最后一步中，我需要在单独列中的MAX（COUNT_LINES）列的SUM，如下所示：

<$ p $ （$（$ COUNT_LINES））
AB 1 5
XB 2 5
XY 2 5

$ p $但是这是我不知道该怎么做的部分。

解决方案

我找不到复制粘贴原始查询两次的解决方案：

  SELECT * FROM（
 SELECT col1，col2，MAX（c）AS m FROM（
 SELECT col1，col2，COUNT（*）AS c 
 FROM table1 
 GROUP BY col1， col2，day，hour 
）distinct_row_count 
 GROUP BY col1，col2 
）AS distinct_row_max INNER JOIN（
 SELECT SUM（m）AS s FROM（
 SELECT col1，col2，MAX（c）AS m FROM（
 SELECT col1，col2，COUNT（*）AS c 
 FROM table1 
 GROUP BY col1，col2，day，hour 
）distinct_row_count 
 GROUP BY col1，col2 
）AS distinct_row_max 
）AS distinct_row_max_sum 
  

输出：

  + ------ + ------ + ------ + ------ + 
 | col1 | col2 | m | s | 
 + ------ + ------ + ------ + ------ + 
 | A | B | 1 | 5 | 
 | X | B | 2 | 5 | 
 | X | Y | 2 | 5 | 
 + ------ + ------ + ------ + ------ + 
  

In MySQL, I would like to have an extra column showing the sum of values of a particular column. However, the numbers I would like to sum come from a subquery and are not stored in a separate table, something like this:

(SELECT a.ID, MAX(a.COUNT_ID) AS MAX_COUNT FROM
    (SELECT ID, COUNT(*) AS COUNT_ID
    FROM my_table
    GROUP BY COL1, COL2) a
 GROUP BY COL1, COL2) b

And this would output something like:

ID     MAX_COUNT
ABC    1
DEF    2
GHI    3

And now, I want an extra column showing the sum of MAX_COUNT, like this (repeated over all rows):

ID     MAX_COUNT    SUM_MAX_COUNT
ABC    1            6
DEF    2            6
GHI    3            6

The actual goal is actually to show the percentage MAX_COUNT of the total MAX_COUNT, so 1/6, 2/6 and 3/6. How do I do this? I already tried doing a CROSS JOIN but it doesn't work:

SELECT * FROM
    ((SELECT a.ID, MAX(a.COUNT_ID) AS MAX_COUNT FROM
        (SELECT ID, COUNT(*) AS COUNT_ID
        FROM my_table
        GROUP BY COL1, COL2) a
     GROUP BY COL1, COL2) b
CROSS JOIN (SELECT SUM(b.MAX_COUNT)) AS c

Error: Unknown table 'b'

---

EXAMPLE

Example table:

CREATE TABLE TABLE1 (
COL1 varchar(255),
COL2 varchar(255),
DAY int,
HOUR int);

INSERT INTO TABLE1 VALUES
('X','Y',1,12),
('X','Y',1,13),
('X','Y',1,13),
('A','B',2,19),
('X','B',3,13),
('X','B',3,13);

Now I want to have, for each combination of COL1 and COL2, the number of lines in this table for each hour:

SELECT COL1, COL2, HOUR, COUNT(*) AS COUNT_LINES
FROM TABLE1
GROUP BY DAY, HOUR, COL1, COL2;

Which outputs this:

COL1    COL2    HOUR    COUNT_LINES
X       Y       12      1
X       Y       13      2
A       B       19      1
X       B       13      2

Now I want, for each combination of COL1 and COL2, the maximum of COUNT_LINES, so I use the query above in a subquery:

SELECT a.COL1, a.COL2, MAX(a.COUNT_LINES)
FROM 
    (SELECT COL1, COL2, HOUR, COUNT(*) AS COUNT_LINES
    FROM TABLE1
    GROUP BY DAY, HOUR, COL1, COL2) a
GROUP BY COL1, COL2;

Which outputs this:

COL1    COL2    MAX(COUNT_LINES)
A       B       1
X       B       2
X       Y       2

Now in the last step, I want the SUM of MAX(COUNT_LINES) column in a separate column, like this:

COL1    COL2    MAX(COUNT_LINES)    SUM(MAX(COUNT_LINES))
A       B       1                   5
X       B       2                   5
X       Y       2                   5

But that is the part I don't know how to do.

解决方案

I could not find a solution other than copy-pasting the original query twice:

SELECT * FROM (
        SELECT col1, col2, MAX(c) AS m FROM (
            SELECT col1, col2, COUNT(*) AS c
            FROM table1
            GROUP BY col1, col2, day, hour
        ) distinct_row_count
        GROUP BY col1, col2
) AS distinct_row_max INNER JOIN (
    SELECT SUM(m) AS s FROM (
        SELECT col1, col2, MAX(c) AS m FROM (
            SELECT col1, col2, COUNT(*) AS c
            FROM table1
            GROUP BY col1, col2, day, hour
        ) distinct_row_count
        GROUP BY col1, col2
    ) AS distinct_row_max
) AS distinct_row_max_sum

Output:

+------+------+------+------+
| col1 | col2 | m    | s    |
+------+------+------+------+
| A    | B    |    1 |    5 |
| X    | B    |    2 |    5 |
| X    | Y    |    2 |    5 |
+------+------+------+------+

这篇关于MySQL结合了COUNT，MAX和SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！