From e6a0371eee20b373d2f09d27f0b44e712d481991 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Thu, 29 Sep 2011 09:23:49 -0400 Subject: [PATCH] Full Circ Count View Amplifies Legacy Circs The current extend_reported.full_circ_count sums the legacy circ count column, but this results in amplifying the count by the number of current circs in the system. This commit adds the legacy count to the GROUP BY instead of summing it. Signed-off-by: Dan Wells Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/extend-reporter.sql | 4 ++-- .../src/sql/Pg/upgrade/XXXX.schema.fix_full_circ_count_view.sql | 7 +++++++ 2 files changed, 9 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_full_circ_count_view.sql diff --git a/Open-ILS/src/sql/Pg/extend-reporter.sql b/Open-ILS/src/sql/Pg/extend-reporter.sql index 6a135682bb..3643631d44 100644 --- a/Open-ILS/src/sql/Pg/extend-reporter.sql +++ b/Open-ILS/src/sql/Pg/extend-reporter.sql @@ -26,12 +26,12 @@ CREATE TABLE extend_reporter.legacy_circ_count ( ); CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS - SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count + SELECT cp.id, COALESCE(c.circ_count, 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count FROM asset."copy" cp LEFT JOIN extend_reporter.legacy_circ_count c USING (id) LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id - GROUP BY cp.id; + GROUP BY cp.id, c.circ_count; CREATE OR REPLACE VIEW extend_reporter.global_bibs_by_holding_update AS SELECT DISTINCT ON (id) id, holding_update, update_type diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_full_circ_count_view.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_full_circ_count_view.sql new file mode 100644 index 0000000000..eb558c32a6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_full_circ_count_view.sql @@ -0,0 +1,7 @@ +CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS + SELECT cp.id, COALESCE(c.circ_count, 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count + FROM asset."copy" cp + LEFT JOIN extend_reporter.legacy_circ_count c USING (id) + LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id + LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id + GROUP BY cp.id, c.circ_count; -- 2.11.0