汇总同一SQL表上的两个条件 [英] Summarizing two conditions on the same SQL table

问题描述

给出一个SQL表

Transactions
  ID            INT
  COMPANY_ID    INT
  STATUS        INT

其中 STATUS IN（0,1）表示免费交易， STATUS IN（2,3）表示可计费交易，每 ANSI SQL语句将向我显示什么简单（我希望） COMPANY_ID ，可结算交易，不可结算交易的数量及其比率？

where STATUS IN (0,1) indicates a free transaction and STATUS IN (2,3) indicates a billable transaction, what simple (I hope) ANSI SQL statement will show me, per COMPANY_ID, the number of billable transactions, non-billable transactions, and their ratio?

如果没有，正确方向的概念产品也可以具体的陈述。我的第一个尝试是使用两个状态组的 WHERE 子句在表上自我联接，但是我被困在如何获取代表每个不同计数的列上，以便我可以计算比率。

A conceptual prod in the right direction is fine if not a specific statement. My first attempts are to self-join on the table with WHERE clauses for the two status groups, but I'm stuck at how to get a column representing each distinct count so that I can compute the ratio.

从概念上讲，它非常类似于 summaryize-aggregated-data ，但我不确定如何将这个问题扩展到这一问题。

This is conceptually very similar to summarize-aggregated-data but I'm not sure how to extend that question to this one.

推荐答案

这是一个开始，我认为这是正确的……唯一剩下的就是增加比率。

Here is a start, I think this is along the right lines...The only thing left would be to add the ratio.

SELECT
    COMPANY_ID,
    NON_BILLABLE = SUM(CASE STATUS WHEN IN (0, 1) THEN 1 ELSE 0 END),
    BILLABLE = SUM(CASE STATUS WHEN IN (2, 3) THEN 1 ELSE 0 END)
FROM TRANSACTIONS
GROUP BY COMPANY_ID

编辑：为

SELECT
    COMPANY_ID,
    SUM(CASE STATUS WHEN IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
    SUM(CASE STATUS WHEN IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
FROM TRANSACTIONS
GROUP BY COMPANY_ID

这篇关于汇总同一SQL表上的两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！