sql - SQL group by 在 sum()

Question

我有以下 sql 语句，它产生以下输出（7/8 DueDate 的过滤结果）

SELECT
    JobType.BillingCategory,
    Jobs.DueDate,
    Sum(Impressions.PRINTtot) AS SumOfPRINTtot,
    Sum(Impressions.PRINTrem) AS SumOfPRINTrem,
    Sum(Impressions.CARDtot) AS SumOfCARDtot,
    Sum(Impressions.CARDrem) AS SumOfCARDrem,
    Sum(Impressions.BOOKtot) AS SumOfBOOKtot,
    Sum(Impressions.BOOKrem) AS SumOfBOOKrem
FROM
    (
        Impressions
        INNER JOIN Jobs ON Impressions.JobNo = Jobs.JobNo
    )
    INNER JOIN JobType ON (Jobs.AccountName = JobType.AccountName)
    AND (Jobs.Product = JobType.Product)
GROUP BY
    Jobs.DueDate,
    JobType.BillingCategory;

我试图在一行中获得所有这些结果：标识符将是 DueDate，并且将为每个 BillingCategory 对 Impressions 表中的值的总和求和。下面的示例（仅出于视觉目的而省略 CARD 和 BOOK 总和，列过多）

标签： sqlsum

Answer 1

您可以使用 CASE 表达式来总结您的数据。您可以修改您的查询以仅针对该计费类别求和，我CARD在下面的示例中用于总结Impressions.PRINTtot和SumOfPRINTrem

SELECT
    Jobs.DueDate,
    Sum(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotCard,
    Sum(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremCard,
    ....<repeat>
FROM
    (
        Impressions
        INNER JOIN Jobs ON Impressions.JobNo = Jobs.JobNo
    )
    INNER JOIN JobType ON (Jobs.AccountName = JobType.AccountName)
    AND (Jobs.Product = JobType.Product)
GROUP BY
    Jobs.DueDate

编辑 1： 基于您的问题中列出的计费类别

一个完整的示例可能如下所示：

SELECT

    Jobs.DueDate,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotCARD,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremCARD,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.CARDtot ELSE 0 END) AS SumOfCARDtotCARD,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.CARDrem ELSE 0 END) AS SumOfCARDremCARD,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.BOOKtot ELSE 0 END) AS SumOfBOOKtotCARD,
SUM(CASE WHEN JobType.BillingCategory='CARD' THEN Impressions.BOOKrem ELSE 0 END) AS SumOfBOOKremCARD,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.CARDtot ELSE 0 END) AS SumOfCARDtotCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.CARDrem ELSE 0 END) AS SumOfCARDremCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.BOOKtot ELSE 0 END) AS SumOfBOOKtotCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='CARDTIPON' THEN Impressions.BOOKrem ELSE 0 END) AS SumOfBOOKremCARDTIPON,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotEOB,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremEOB,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.CARDtot ELSE 0 END) AS SumOfCARDtotEOB,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.CARDrem ELSE 0 END) AS SumOfCARDremEOB,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.BOOKtot ELSE 0 END) AS SumOfBOOKtotEOB,
SUM(CASE WHEN JobType.BillingCategory='EOB' THEN Impressions.BOOKrem ELSE 0 END) AS SumOfBOOKremEOB,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.CARDtot ELSE 0 END) AS SumOfCARDtotMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.CARDrem ELSE 0 END) AS SumOfCARDremMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.BOOKtot ELSE 0 END) AS SumOfBOOKtotMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDE' THEN Impressions.BOOKrem ELSE 0 END) AS SumOfBOOKremMEMBERGUIDE,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.PRINTtot ELSE 0 END) AS SumOfPRINTtotMEMBERGUIDEHD,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.PRINTrem ELSE 0 END) AS SumOfPRINTremMEMBERGUIDEHD,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.CARDtot ELSE 0 END) AS SumOfCARDtotMEMBERGUIDEHD,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.CARDrem ELSE 0 END) AS SumOfCARDremMEMBERGUIDEHD,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.BOOKtot ELSE 0 END) AS SumOfBOOKtotMEMBERGUIDEHD,
SUM(CASE WHEN JobType.BillingCategory='MEMBERGUIDEHD' THEN Impressions.BOOKrem ELSE 0 END) AS SumOfBOOKremMEMBERGUIDEHD

FROM
    (
        Impressions
        INNER JOIN Jobs ON Impressions.JobNo = Jobs.JobNo
    )
    INNER JOIN JobType ON (Jobs.AccountName = JobType.AccountName)
    AND (Jobs.Product = JobType.Product)
GROUP BY
    Jobs.DueDate

使用特定的数据库/工具，可能会有各种可能被证明有用的功能。但是，我发现在这些情况下，特别是因为您的计费类别可能会随着时间的推移而变化，因此您可以在任何地方运行的脚本有时会很有帮助。我已经包含了我用来生成下面代码的脚本

var types='CARD,CARDTIPON,EOB,MEMBERGUIDE,MEMBERGUIDEHD'.split(',');
var metrics = metrics='PRINTtot,PRINTrem,CARDtot,CARDrem,BOOKtot,BOOKrem'.split(',');
var metricTemplate="SUM(CASE WHEN JobType.BillingCategory='[TYPE]' THEN Impressions.[METRICNAME] ELSE 0 END) AS SumOf[METRICNAME][TYPE]";

var summary_lines = []
for(var i=0;i < types.length;i++){
    for(var j=0;j<metrics.length;j++){
       summary_lines.push(metricTemplate.replaceAll('[TYPE]',types[i]).replaceAll('[METRICNAME]',metrics[j]))
    }
}

complete_metrics = summary_lines.join(",\n");

console.log(complete_metrics)