CODEWITHSUNDEEP
sqlms-access
Jessica Tylka
Jessica Tylka

Reputation: 23

"Your query does not include the specified expression..."

I have tried endless things to get this to work and it seems to break over and over again and not work. I'm trying to GROUP BY product after I have calculated the field quantity returned/quantity ordered, but I get the error

your query does not include the specified expression 'quantity_returned/quantity_ordered' as part of an aggregate function.

I do not want to GROUP BY quantity_returned, quantity_ordered, and product, I only want to GROUP BY product.

Here's what my SQL looks like currently...

SELECT 
    quantity_returned/quantity_ordered AS percentage_returned, 
    quantity_returned, 
    quantity_ordered, 
    returns_fact.product
FROM 
    Customer_dimension 
    INNER JOIN 
    (
        Product_dimension 
        INNER JOIN 
        (
            Day_dimension 
            INNER JOIN 
            returns_fact 
                ON Day_dimension.day_key = returns_fact.day_key
        ) 
            ON Product_dimension.product_key = returns_fact.product_key
    ) 
        ON Customer_dimension.customer_key = returns_fact.customer_key
GROUP BY returns_fact.product;

Upvotes: 0

Views: 479

Answers (1)

Michael
Michael

Reputation: 184

When you use a group by you need to actually include everything in your select that isn't a aggregate function.

I have no idea how your tables are set up, but I am throwing a blind dart. If you provide fields in each of the 4 tables someone will be better able to help.

SELECT returns_fact.product, count(quantity_returned), count(quantity_ordered), count(quantity_returned)/count(quantity_ordered) as percentage returned

Upvotes: 0

Related Questions