时间戳

首页 > 解决方案 > 如何获取总行数和特定列的总和

sql - 如何获取总行数和特定列的总和

问题描述

这是我的原始查询

SELECT acct_id,
       acct_client_short_name,
       acct_cat_code,
       cusip_num,
       symbol_code,
       product_name,
       sec_type_code,
       acct_type_code,
       ann_int_rate_and_maturity_dt_text,
       sd_id,
       loc_code,
       settled_flag,
       td_id,
       eff_dt,
       clearing_code,
       SUM(traded_shrs_num),
       SUM(curr_shrs_num) AS c1
FROM (SELECT 'ID900910' AS acct_id,
             'CNS Netting' AS acct_client_short_name,
             'CNS' AS acct_cat_code,
             cusip_num,
             symbol_code,
             product_name,
             sec_type_code,
             acct_type_code,
             ann_int_rate_and_maturity_dt_text,
             sd_id,
             loc_code,
             settled_flag,
             td_id,
             eff_dt,
             clearing_code,
             traded_shrs_num,
             curr_shrs_num
      FROM [csr_staging].[dbo].[fi_impact_source]
      WHERE (clearing_code = 'MBS'
          OR clearing_code = 'CNS')
        AND (SD_ID >= EFF_DT)) a
GROUP BY acct_id,
         acct_client_short_name,
         acct_cat_code,
         cusip_num,
         symbol_code,
         product_name,
         sec_type_code,
         acct_type_code,
         ann_int_rate_and_maturity_dt_text,
         sd_id,
         loc_code,
         settled_flag,
         td_id,
         eff_dt,
         clearing_code;

总行数 = 383 行

我们如何从上述查询中获取总行数？

即select count(*),sum(c1) from query1？

标签： sqlsql-servertsql

解决方案

你应该这样写：

SELECT count(*), sum(c1) FROM (SELECT ...) x

换句话说：

SELECT count(*), sum(c1)
FROM (
SELECT acct_id,
       acct_client_short_name,
       acct_cat_code,
       cusip_num,
       symbol_code,
       product_name,
       sec_type_code,
       acct_type_code,
       ann_int_rate_and_maturity_dt_text,
       sd_id,
       loc_code,
       settled_flag,
       td_id,
       eff_dt,
       clearing_code,
       SUM(traded_shrs_num) AS c2,
       SUM(curr_shrs_num) AS c1
FROM (SELECT 'ID900910' AS acct_id,
             'CNS Netting' AS acct_client_short_name,
             'CNS' AS acct_cat_code,
             cusip_num,
             symbol_code,
             product_name,
             sec_type_code,
             acct_type_code,
             ann_int_rate_and_maturity_dt_text,
             sd_id,
             loc_code,
             settled_flag,
             td_id,
             eff_dt,
             clearing_code,
             traded_shrs_num,
             curr_shrs_num
      FROM [csr_staging].[dbo].[fi_impact_source]
      WHERE (clearing_code = 'MBS'
          OR clearing_code = 'CNS')
        AND (SD_ID >= EFF_DT)) a
GROUP BY acct_id,
         acct_client_short_name,
         acct_cat_code,
         cusip_num,
         symbol_code,
         product_name,
         sec_type_code,
         acct_type_code,
         ann_int_rate_and_maturity_dt_text,
         sd_id,
         loc_code,
         settled_flag,
         td_id,
         eff_dt,
         clearing_code
) x

这称为使用“派生表”。

推荐阅读