the best and fastest way I've found to do this is by...
set var vC int=0
set var vIcnt int=0
set var vIAcnt int=0
drop table attCnt
create temp table attCnt +
(`Category` INT , +
`ITEMS` INT, +
`ACTITEMS` INT +
)
insert into attCnt (Category) select distinct Category FROM categories
DROP CURSOR GI
DECLARE GP CURSOR FOR SELECT DISTINCT Category FROM attCnt
OPEN GI
FETCH GI INTO vC
WHILE SQLCODE <>100 THEN
set var vIcnt int=0
set var vIAcnt int=0
SELECT COUNT(*) INTO vIcnt FROM items where category=.vC
SELECT COUNT(*) INTO vIAcnt FROM items where category=.vC and status =
'ACTIVE'
update attCnt set items=(.vIcnt), actitems=(.vIAcnt) where category=.vC
FETCH GI INTO vC
ENDWHILE
DROP CURSOR GI
----- Original Message -----
From: Jason Kramer
To: RBASE-L Mailing List
Sent: Wednesday, December 02, 2009 1:38 PM
Subject: [RBASE-L] - SQL COUNT question
I have two tables, categories and items. A category can have zero to many
items, and each item can be active or inactive. I want to produce a view that
will show how many items, and how many active items are in each category. So
far the only way I have been able to do so is with two intermediate/precursor
views. I am looking to generate output like:
Category Items ActiveItems
1 10 10
2 0 0
3 45 42
4 14 0
etc...
I have my two precursor views count items and active items and then UNION
with categories to catch any zero count categories. Then I build a new view
from the first two. Is there a more efficient way to do this? My SQL is below.
ItemsView
SELECT category AS Category,(0) AS Items FROM categories WHERE category NOT
IN (SELECT category FROM items) UNION SELECT category AS Category,COUNT item AS
Items FROM items GROUP BY category ORDER BY category
ActiveItemsView
SELECT category AS Category,(0) AS ActiveItems FROM categories WHERE category
NOT IN (SELECT category FROM items) UNION SELECT category AS Category,COUNT
item AS ActiveItems FROM items WHERE status = 'ACTIVE' GROUP BY category ORDER
BY category
FinalView
SELECT T1.Category,T2.Items,T3.ActiveItems FROM categories T1, ItemsView
T2,ActiveItemsView T3 WHERE T1.Category = T2.Category AND T1.Category =
T3.Category
Thanks,
Jason
--
Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)
