4

Given a SQL table

Transactions
  ID            INT
  COMPANY_ID    INT
  STATUS        INT

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?

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.

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

Community
  • 1
  • 1
Eric J.
  • 147,927
  • 63
  • 340
  • 553

1 Answers1

5

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

EDIT: for standards compliance.

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
Wil P
  • 3,341
  • 1
  • 20
  • 20
  • I guess SUM(CASE... END) AS BILLABLE is more standards compliant. – Dmitry Nov 24 '09 at 00:43
  • Thanks for this start. I'll try it out as soon as I get in tomorrow. – Eric J. Nov 24 '09 at 01:32
  • My actual DB is MySQL and I had to reverse the order of "STATUS" and "WHEN" for the query to work (using the Standards Compliant one) but then it works like a charm. – Eric J. Nov 24 '09 at 17:48