计算总行数并按mysql中的列分组 [英] Counting total rows and grouping by a column in mysql

问题描述

所以我一直在看这个，但我似乎无法弄清楚.

So i've been looking at this one for a while and I can't seem to figure it out.

我有一个具有以下格式和示例数据的 mysql 表:

I have a mysql table with the following format and sample data:

ID, Customer, Time, Error Code, Duration

1,Test1,00:12:00,400,120
2,Test2,00:14:00,404,60
3,Test1,00:15:00,404,120
4,Test2,00:17:00,503,120
5,Test1,00:19:00,400,60
6,Test1,00:20:00,400,60
7,Test2,00:21:00,503,60

我需要结果包含客户名称、每个客户名称的总行数、每个客户的错误代码、每个客户的错误数量以及持续时间的总和.因此，对于上述结果，我想得到:

I need the results to have the customer name, total number of rows per Customer name, the error codes per customer, then the number of errors per customer and then sum the duration. So for the above results I would want to get:

Test1, 4, 400, 3, 360
Test1, 4, 404, 1, 360
Test2, 2, 404, 1, 240
Test2, 2, 503, 2, 240

我遇到的问题是当我按错误代码分组时，行数的计数和持续时间的总和也按该错误代码分组.我需要客户的总行数和持续时间，而不是每个错误代码的客户总行数和持续时间.

The problem i am having is when i group by the error code, the count for the number of rows and the sum of the duration is also grouped by that error code. I need the total count of rows and duration for the customer not the total count of rows and duration for the customer per error code.

如果我需要包含任何其他内容，或者我是否非常困惑，请告诉我，我可能是.

Please let me know if I need to include anything else or if i'm super confusing which I probably am.

提前感谢您的帮助！

推荐答案

首先需要 group by 来获取计算列

First you need group by to get the calculated columns

 SELECT Customer, COUNT(*) as total_count, SUM(duration) as total_duration
 FROM yourTable
 GROUP BY  Customer

和

 SELECT Customer, Error, COUNT(*) as error_count
 FROM yourTable
 GROUP BY  Customer, Error

然后回到你的桌子上

SELECT t1.Customer, 
       t2.total_count, 
       t1.error, 
       t3.error_count, 
       t2.total_duration
FROM yourTable as t1
JOIN (
     SELECT Customer, COUNT(*) as total_count, SUM(duration) as total_duration
     FROM yourTable
     GROUP BY  Customer
     ) as t2
  ON t1.Customer = t2.Customer
JOIN (
     SELECT Customer, Error, COUNT(*) as error_count
     FROM yourTable
     GROUP BY  Customer, Error
) as t3
 ON t1.Customer = t3.Customer
AND t1.Error = t3.Error
GROUP BY t1.Customer
       , t1.Error

这篇关于计算总行数并按mysql中的列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！