Question

I need to create a report (seen below), using Jasper, using the following table example. The main problem is the Count(A) and Count(C). I solved this problem before using the Oracle temporary table feature (using merge).

我期待以更有效的/立法方式来完成这项任务,无论是通过《杰斯珀报告》还是通过《杰斯珀报告》。

CUSTOMER Table
CustomerID | CustomerType |  Status   |   Amount
---------------------------------------------------
1111       |  C           | A         | 10.5
1112       |  C           | C         |  3.0
1113       |  C           | A         | 6.0
1114       |  C           | A         | 5.0
1115       |  C           | A         | 5.0
1116       |  R           | C         | 4.0
1117       |  R           |C          | 5.0
1118       |  R           |C          | 6.0
1119       |  R           |A          | 7.0
1120       |  R           |A          | 3.0

Report
Customer Type |   Count   |  Count(C) |   Count (A)  |   Sum(amount)
C             |   5       |       1   |     4        |   29.5
R             |   5       |       3   |     2        |   25.0

Answer 1

With SQL:

SELECT
    CustomerType,
    COUNT(*),
    SUM(CASE WHEN Status =  C  THEN 1 ELSE 0 END),
    SUM(CASE WHEN Status =  A  THEN 1 ELSE 0 END),
    SUM(Amount)
FROM
    Customers
GROUP BY
    CustomerType
ORDER BY
    CustomerType

友情链接