[RBASE-L] - Re: SQL COUNT question

[Jason Kramer]

Thanks for your help.  I added a GROUP BY at the end, and it works perfectly.  I was pretty sure there was an easier way.                                   Thanks,                                   Jason Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) Lawrence Lustig wrote: <<     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?   >> No intermediate views are necessary: SELECT Category, COUNT(Items.ItemID) AS TotalItems, SUM(IFEQ(Items.Status, 'ACTIVE', 1, 0)) AS ActiveItems  +    FROM Categories LEFT OUTER JOIN Items    ON Categories.Category = Items.Category This joins Categories and Items (including categories with no items). TotalItems is calculated as the number of non-NULL Items rows for each category. ActiveItems is calculated by adding 1 for each each row where Status = 'ACTIVE' -- Larry