Compare counts from separate rows?

[BENDY]

Hi,

I've got a challenge that requires comparing the results of counts from different rows. Its easier to explain if I set the scene.

Our customer wants to know the DELTA of equipment installed on a Site. Each peice of kit can be Reused (i.e. second-hand). Multiple versions of the configuration can be recorded. At any tme only one version will be Current, but that may consist of multiple parts.

OK, heres the simplified source data


SITEID VERSION VERSIONSTATUS CABID REUSED
====== ======= ============= ===== ======
BTS1 0 Historical 1
BTS1 1 Current 1 1
BTS1 1 Current 2 1
BTS1 2 Planned 1
BTS1 2 Planned 2
BTS1 3 Planned 1 1
BTS1 3 Planned 2 1
BTS1 3 Planned 3 1
BTS1 3 Planned 4


From this I can get the number of Reused and non-Reused records per version.


SQL> select siteid,
version,
versionstatus,
count(*) - count(reused) "NEW",
count(reused) "REUSED"
from count_example
group by siteid, version, versionstatus ;

SITEID VERSION VERSIONSTATUS NEW REUSED
====== ======= ============= === ======
BTS1 0 Historical 1 0
BTS1 1 Current 0 2
BTS1 2 Planned 2 0
BTS1 3 Planned 1 3


OK, thats the easy bit. No what I need is to calculate the difference between any version and the 'Current'. I.e. Subtract the values found for 'Current' version from the values found in the selected row.

The expected output would be:

SITEID VERSION VERSIONSTATUS DNEW DREUSED
====== ======= ============= ==== ======
BTS1 0 Historical 1 -2 (e.g. 1-0, 0-2)
BTS1 1 Current 0 0 (e.g. 0-0, 2-2)
BTS1 2 Planned 2 -2 (e.g. 2-0, 0-2)
BTS1 3 Planned 1 1 (e.g. 1-0, 3-2)


I tried using self-joins with separate counts from both tables. The join was on A.SITEID=B.SITEID and B.VERSIONSTATUS='Current'. The problem is that different versions have different number of rows which throws things out.

Another option was to use views, but this example is only one of 6 similar tables that has to be compared.

I have been looking at analytic functions and they look promising but I not familiar with the concepts and am seriously struggling to find a solution.

Any help or advice greatly appreciated.

Ben

[amir]

SQL> select a.siteid,
a.version,
a.versionstatus,
count(*) - count(a.reused) "DNEW",
(count(*) - count(a.reused)) - (select count(*) - count(b.reused) from count_example b where b.versionstatus='Current') "DREUSED"
from count_example a
group by a.siteid, a.version, a.versionstatus ;

[amir]

Originally posted by amir
SQL> select a.siteid,
a.version,
a.versionstatus,
count(*) - count(a.reused) "DNEW",
(count(*) - count(a.reused)) - (select count(*) - count(b.reused) from count_example b where b.versionstatus='Current') "DREUSED"
from count_example a
group by a.siteid, a.version, a.versionstatus ;

Will it suffice?

[arjun]

Originally posted by amir
SQL> select a.siteid,
a.version,
a.versionstatus,
count(*) - count(a.reused) "DNEW",
(count(*) - count(a.reused)) - (select count(*) - count(b.reused) from count_example b where b.versionstatus='Current') "DREUSED"
from count_example a
group by a.siteid, a.version, a.versionstatus ;

THE ABOVE SOLUTION IS NOT CORRECT. IT WILL THROUGH A GROUP ERROR.

select siteid,
version,
versionstatus,
count(*) - count(reused) "DNEW",
count(reused)-a.dreused "DREUSED"
from count_example, (select count(reused) dreused from count_example where versionstatus = 'Current') a
group by siteid, version, versionstatus, a.dreused ;


THIS ONE SHOULD DO IT.

[BENDY]

Thanks guys.

I can put the Asprin back in the drawer now :D

Ben