SQL Server 将所有求和结果合并到一行并保留求和值

ktca8awb  于 2022-12-10  发布在  其他
关注(0)|答案(1)|浏览(144)

I am trying to build a query which gets sum of different kinds of replications per user. I want to show a unified result with rows with each replication and another row which sums them into an all per user. Here is the query:

SELECT replica_name,
       user_id,
       short_name,
       number_of_replications,
       firstReplication,
       lastReplication
FROM   (SELECT 'Initial'            AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'initial'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'Delta'              AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'delta'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'All'                AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) <> 'upload'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type) AS t
WHERE  short_name = 'BY060955'
ORDER  BY replica_name ASC,
          number_of_replications DESC

this is the result:

replica_name user_id short_name number_of_replications firstReplication             LastReplication
All          22472   BY060955   836                    2022-11-14 06:26:05.2415463  2022-12-08 10:25:17.4282712
All          22472   BY060955   2                      2022-11-14 06:25:08.2385837  2022-11-16 11:55:41.0263526
Delta        22472   BY060955   836                    2022-11-14 06:26:05.2415463  2022-12-08 10:25:17.4282712
Initial      22472   BY060955   2                      2022-11-14 06:25:08.2385837  2022-11-16 11:55:41.0263526

I want for 'All' to be a single row (836+2 = 838) but'Delta' and 'Initial' to stay as they are

ssgvzors

ssgvzors1#

从最后一个查询中,删除按sc.synchro_type分组:

SELECT 'All'                AS replica_name,
           sc.user_id           AS user_id,
           u.short_name         AS short_name,
           Count(sc.user_id)    AS number_of_replications,
           Min(sc.connected_at) AS firstReplication,
           Max(sc.connected_at) AS lastReplication
    FROM   phoenix.synchro_connections sc
           JOIN phoenix.users u
             ON u.user_id = sc.user_id
    WHERE  Lower(sc.synchro_type) <> 'upload'
           AND  sc.size_in_bytes IS NOT NULL
    GROUP  BY sc.user_id,
              u.short_name

相关问题