CODEWITHSUNDEEP
sqlcube
user3116949
user3116949

Reputation: 275

For each consumer find the cheapest product that was purchased

I am trying to obtain a list of the cheapest product that was purchased for by each consumer from a cube. I tried the following cube query:

SELECT "Consumer Name","Product Name",MIN([Total Price]) AS "Total Purchase Price"
FROM Transaction_Cube 
WHERE "Consumer Name" IS NOT NULL
    AND "Consumer City" IS NOT NULL
    AND "Consumer State" IS NOT NULL       
    AND "Product Name" IS NOT NULL 
    AND "Product Category" IS NOT NULL
    AND "Product Line" IS NOT NULL
    AND "Product Packaging" IS NOT NULL
GROUP BY "Consumer Name","Product Name"

But that is giving me the following results:

enter image description here

where I am getting the lowest purchased price of each consumer/product combination. But what I am trying to achieve is something like this:

enter image description here

where I have for each consumer only the cheapest product that was purchased. Any ideas or suggestions on how to correct this query would be truly appreciated!

Upvotes: 0

Views: 196

Answers (1)

forpas
forpas

Reputation: 164139

You don'n need to GROUP BY "Consumer Name","Product Name".
Check this:

SELECT t.[Consumer Name], t.[Product Name], t.[Total Price] AS "Total Purchase Price"
FROM Tb_Transactions_Cube_FE t
WHERE 
t.[Total Price] = (SELECT MIN([Total Price]) FROM Tb_Transactions_Cube_FE WHERE [Consumer Name] = t.[Consumer Name])
ORDER BY t.[Consumer Name]

Upvotes: 1

Related Questions