Equivalent of group by in SQL for MDX query

Question

I have a query in SQL that looks like the following:

SELECT "Supplier City", "Consumer City",SUM([Number of Transactions]) AS "Number of Transactions"
FROM Tb_Transactions_Cube 
WHERE "Supplier Name" IS NOT NULL
    AND "Supplier City" IS NOT NULL
    AND "Supplier State" IS NOT NULL    
    AND "Consumer Name" IS NOT NULL    
    AND "Consumer City" IS NOT NULL    
    AND "Consumer State" IS NOT NULL    
    AND "Product Name" IS NULL
GROUP BY "Supplier City", "Consumer City"

This query returns the number of transactions between supplier-city-consumer-city pairs. I am trying to create an equivalent of this query in MDX but I seem to be stuck on how to apply the group by to the MDX query. What I have till now in mdx looks like the following:

SELECT NON EMPTY { 
                    SUM([Measures].[Tb Transactions Count]) AS "Number of Transactions" } ON COLUMNS,        
        NON EMPTY {  
                    [Tb Supplier].[City].[City].ALLMEMBERS,
                    [Tb Consumer].[City].[City].ALLMEMBERS } ON ROWS
FROM [Table]

I am new to mdx so not sure if this is the correct approach and how to apply the group by to my current query. Any help or insights to this will be extremely helpful.

Answer 1

Your query will have the result you are expecting. i don't know of any other way to do this aggregation in MDX.