CODEWITHSUNDEEP
sqlolapcube
Cameron Erwin
Cameron Erwin

Reputation: 51

Trying to find distinct values in SQL OLAP Cube

I'm trying to find an answer to this question: For each supplier city find the product offered in largest quantity?

I have each city with the products and quantities for each product See code output below: enter image description here

Here is the code for the query:

SELECT [Supplier City], [Product Name], [Total Transactions Quantity]
FROM tb_final_cube
WHERE "Supplier Name" IS NULL
AND "Supplier City" IS NOT NULL
AND "Supplier State" IS NOT NULL
AND "Product Name" IS NOT NULL
AND "Product Packaging" IS NOT NULL
AND "Product Line" IS NULL
AND "Product Category" IS NULL

And finally here is the code for the cube I'm using:

SELECT DISTINCT s.name "Supplier Name",
                s.city "Supplier City",
                s.state "Supplier State",

                p.name "Product Name",
                p.product_category "Product Category",
                p.product_line "Product Line",
                p.product_packaging "Product Packaging",

                SUM(Quantity) "Total Transaction Quantity",
                SUM(Quantity * Price) "Total Sales Amount",
                MAX(Price) "Maximum Price",
                MIN(Price) "Minimum Price"
        INTO tb_final_cube
        FROM tb_supplier s, tb_product p tb_offers t
        WHERE s.supp_id=t.supp_id
        AND p.prod_id=t.prod_id
        GROUP BY CUBE ((s.Name, s.City, s.State), (p.Name, p.Product_Category, p.Product_Line)),
         ROLLUP (s.state, s.city, s.name),
         ROLLUP (p.Product_Packaging, p.Name),
         ROLLUP (p.Product_Category, p.Product_Line, p.Name)

Upvotes: 1

Views: 737

Answers (3)

DSstudent
DSstudent

Reputation: 11

Reviving this thread because it is the top result for a very specific google search. This was my solution utilizing data cube query format: 

    SELECT [Supplier City], [Product Name], [Total Quantity] "Top Product"
FROM(
    SELECT  [Supplier City], [Product Name], [Total Quantity],DENSE_RANK() OVER (PARTITION BY [Supplier City] ORDER BY [Total Quantity] DESC) "rnk"
    FROM(       
            SELECT [Supplier City], [Product Name], [Total Quantity]
            FROM Tb_Final_Cube
            WHERE "Product Name" IS NOT NULL
            AND "Supplier City" IS NOT NULL
            AND "Supplier State" IS NOT NULL
            AND "Supplier Name" IS NULL
            GROUP BY [Supplier City], [Product Name], [Total Quantity]
            ) AS R
            ) AS T
WHERE rnk = 1

This queries the cube in the format you've more than likely been using in class then appends a rank to each product based on city. Good luck on your finals.

Upvotes: 1

Arun Prasan
Arun Prasan

Reputation: 31

SELECT DT1.city, DT2.product, DT2.qty
FROM (SELECT city, max(qty) as qty
      FROM arunslocal.tb_final_cube
      group by city) AS DT1, (SELECT city,qty, product
                              FROM arunslocal.tb_final_cube) AS DT2
WHERE DT1.city = DT2.city AND DT1.qty = DT2.qty

Upvotes: 0

Arun Prasan
Arun Prasan

Reputation: 31

Derived Table 1 => SELECT CITY, MAX(QTY) Derived Table 2 => SELECT CITY, PRODUCT, QTY Link both with CITY AND QTY and get the PRODUCT NAME.

Upvotes: 0

Related Questions