对分组字段求和

发布于 2024-11-19 01:07:35 字数 992 浏览 6 评论 0原文

我需要对已经使用 GROUP BY 计算出的列的内容进行求和。您具体会如何处理？

该组应该基于用户名，而不是结果集的全部内容。我相信这本质上是该用户名字段上的分组，但我相信这会破坏查询当前的工作方式。

下面的示例：

   SELECT A1.USERNAME, DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP),'%Y-%m-%d') AS DTTM, A1.ACCTSESSIONID,
          MAX(IFNULL(A1.ACCTINPUTGW,0)  * POW(2,32) + IFNULL(A1.ACCTINPUTOCT, 0)) - MAX(IFNULL(A2.ACCTINPUTGW,0)  * POW(2,32) + IFNULL(A2.ACCTINPUTOCT, 0)) as TOTAL_UPLOAD,
          MAX(IFNULL(A1.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTOUTPUTOCT, 0)) - MAX(IFNULL(A2.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTOUTPUTOCT, 0)) as TOTAL_DOWNLOAD

     FROM ACCOUNTING A1   

LEFT JOIN ACCOUNTING A2
       ON A1.ACCTSESSIONID = A2.ACCTSESSIONID
      AND DATE_FORMAT(FROM_UNIXTIME(A2.TIME_STAMP), '%Y-%m-%d') = '2011-07-04'   

    WHERE DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP), '%Y-%m-%d') = '2011-07-05'   
 GROUP BY A1.ACCTSESSIONID,A2.ACCTSESSIONID
 ORDER BY A1.USERNAME

编辑：

列将是：TOTAL_DOWNLOAD 和 TOTAL_UPLOAD

原文

I need to SUM the contents of a column which is already worked out using GROUP BYs.. How exactly would you go about that?

The group should be based on the user name, not the entire contents of the result set. I believe this essentially a group by on that username field, but that i believe would break how the query currently works..

Example below:

   SELECT A1.USERNAME, DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP),'%Y-%m-%d') AS DTTM, A1.ACCTSESSIONID,
          MAX(IFNULL(A1.ACCTINPUTGW,0)  * POW(2,32) + IFNULL(A1.ACCTINPUTOCT, 0)) - MAX(IFNULL(A2.ACCTINPUTGW,0)  * POW(2,32) + IFNULL(A2.ACCTINPUTOCT, 0)) as TOTAL_UPLOAD,
          MAX(IFNULL(A1.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTOUTPUTOCT, 0)) - MAX(IFNULL(A2.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTOUTPUTOCT, 0)) as TOTAL_DOWNLOAD

     FROM ACCOUNTING A1   

LEFT JOIN ACCOUNTING A2
       ON A1.ACCTSESSIONID = A2.ACCTSESSIONID
      AND DATE_FORMAT(FROM_UNIXTIME(A2.TIME_STAMP), '%Y-%m-%d') = '2011-07-04'   

    WHERE DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP), '%Y-%m-%d') = '2011-07-05'   
 GROUP BY A1.ACCTSESSIONID,A2.ACCTSESSIONID
 ORDER BY A1.USERNAME

Edit:

The columns would be: TOTAL_DOWNLOAD and TOTAL_UPLOAD

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

信仰 2024-11-26 01:07:35

谢谢@ypercube，辛苦了

SELECT A3.USERNAME
     , SUM(A3.TOTAL_UPLOAD) AS FINAL_UPLOAD
     , SUM(A3.TOTAL_DOWNLOAD) AS FINAL_DOWNLOAD
FROM 
  ( SELECT
        A1.USERNAME
      , DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP),'%Y-%m-%d') AS DTTM
      , A1.ACCTSESSIONID
      , MAX(IFNULL(A1.ACCTINPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTINPUTOCT, 0))
        - MAX(IFNULL(A2.ACCTINPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTINPUTOCT, 0))
        AS TOTAL_UPLOAD
      , MAX(IFNULL(A1.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTOUTPUTOCT, 0))
        - MAX(IFNULL(A2.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTOUTPUTOCT, 0))
        AS TOTAL_DOWNLOAD             
    FROM ACCOUNTING A1   
      LEFT JOIN ACCOUNTING A2
        ON A1.ACCTSESSIONID = A2.ACCTSESSIONID
        AND DATE_FORMAT(FROM_UNIXTIME(A2.TIME_STAMP), '%Y-%m-%d') = '2011-07-04'   
    WHERE DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP), '%Y-%m-%d') = '2011-07-05'   
    GROUP BY A1.ACCTSESSIONID,A2.ACCTSESSIONID
    ORDER BY A1.USERNAME
  ) AS A3    
GROUP BY A3.USERNAME

Thanks @ ypercube, worked a treat

SELECT A3.USERNAME
     , SUM(A3.TOTAL_UPLOAD) AS FINAL_UPLOAD
     , SUM(A3.TOTAL_DOWNLOAD) AS FINAL_DOWNLOAD
FROM 
  ( SELECT
        A1.USERNAME
      , DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP),'%Y-%m-%d') AS DTTM
      , A1.ACCTSESSIONID
      , MAX(IFNULL(A1.ACCTINPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTINPUTOCT, 0))
        - MAX(IFNULL(A2.ACCTINPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTINPUTOCT, 0))
        AS TOTAL_UPLOAD
      , MAX(IFNULL(A1.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A1.ACCTOUTPUTOCT, 0))
        - MAX(IFNULL(A2.ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(A2.ACCTOUTPUTOCT, 0))
        AS TOTAL_DOWNLOAD             
    FROM ACCOUNTING A1   
      LEFT JOIN ACCOUNTING A2
        ON A1.ACCTSESSIONID = A2.ACCTSESSIONID
        AND DATE_FORMAT(FROM_UNIXTIME(A2.TIME_STAMP), '%Y-%m-%d') = '2011-07-04'   
    WHERE DATE_FORMAT(FROM_UNIXTIME(A1.TIME_STAMP), '%Y-%m-%d') = '2011-07-05'   
    GROUP BY A1.ACCTSESSIONID,A2.ACCTSESSIONID
    ORDER BY A1.USERNAME
  ) AS A3    
GROUP BY A3.USERNAME

回复收藏 0 原文

~没有更多了~