<<
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
