I have two tables in Vertica Database
1) cross join of all existing locations and categories
location_1 category_1
location_1 category_2
...
location_2 category_1
location_2 category_2
...
location_n category_n-1
location_n category_n
2) list of items with locations and categories known:
item_1 location_1 category_4
item_2 location_8 category_3
...
I want to calculate, how many items is there in every location+category combination. How can I do this?
One possible approach:
SELECT c.location, c.category, COUNT(i.item)
FROM crosses c
LEFT JOIN items i
ON i.location = c.location AND i.category = c.category
GROUP BY c.location, c.category
The idea is the result of LEFT JOIN will have all the locations and categories from crosses table - but rows for missing items will be NULL-ed.
i think this approach will work for you
select Cross_j.location,Cross_j.category,count(item) from items
inner join
(
SELECT e.location,e.category FROM crosses as e CROSS JOIN crosses as w
) Cross_j
on Cross_j.location=items.location and Cross_j.category=items.category
group by Cross_j.location,Cross_j.category
