| Logic |
To get our desired result format, we wrap IIF function inside SUM function, and then wrap SUM function inside NZ function. If any result of the SUM function is null, NZ converts it to 0.
The first SUM will add up the UnitsInStock for each country that belongs to Europe by using IIF function inside the SUM function. The second SUM applies the same logic for America. The last SUM does it against Asia-Pacific countries.
In GROUP BY, we specify product category so that only the same category is grouped.
|
| SQL View: |
SELECT Categories.CategoryName,
NZ(Sum(IIf([suppliers].[Country] In (
'UK',
'Spain',
'Sweden',
'Germany',
'Norway',
'Denmark',
'Netherlands',
'Finland',
'Italy',
'France'),[products].[unitsinstock])),0) AS Europe,
NZ(Sum(IIf([suppliers].[country] In (
'USA',
'Canada',
'Brazil'),[products].[unitsinstock])),0) AS America,
NZ(Sum(IIf([suppliers].[country] In (
'Japan',
'Australia',
'Singapore'),[products].[unitsinstock])),0) AS [Asia-Pacific]
FROM Suppliers INNER JOIN (Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID
GROUP BY Categories.CategoryName;
|
