CODEWITHSUNDEEP
sqldatabasems-access
Bob P
Bob P

Reputation: 237

Getting an error when running an SQL Sum statement in Access

I am trying to find out the total cost of all items in a certain table of my database, the cost is stored in another table but the product codes provide links so this is not an issue.

However my issue comes when trying to also get Access to display the Product Code and Description of each item.

Here is my code:

   SELECT [Product Code], Description, Sum([Purchase Price]) AS Total
    FROM [products/stock] AS p 
     INNER JOIN [Stock Conversion Items] AS s 
     ON p.[Product Code] = s.[Result PC]
   WHERE s.CutID = [Enter your Cut ID here]
   GROUP BY s.SCID;

As you can see, I have chosen what data to display, joined the tables, used [Enter your Cut ID here] to get information from the user and then grouped the information by SCID (A key to denote a single case of items).

Here is the error message:

Error message

Any help would be massively appreciated,
Thanks

Upvotes: 0

Views: 1877

Answers (3)

Neil Knight
Neil Knight

Reputation: 48537

When using an aggregate function, like SUM, you will need to GROUP BY all of the other selected columns. So, your GROUP BY should be:

GROUP BY S.[SCID], [Product Code], Description;

Upvotes: 1

Jeroen Vuurens
Jeroen Vuurens

Reputation: 1251

By definition, if you use an aggregated query, all columns you use without an aggregation function (like SUM, COUNT, etc), must be in your group by clause. It does not have to be the other way around, so you can have column in your group by without using them anywhere else.

  SELECT [Product Code], Description, Sum([Purchase Price]) AS Total 
    FROM [products/stock] AS p  
     INNER JOIN [Stock Conversion Items] AS s  
     ON p.[Product Code] = s.[Result PC] 
   WHERE s.CutID = [Enter your Cut ID here] 
   GROUP BY S.SCID, [Product Code], Description;

Upvotes: 2

John Woo
John Woo

Reputation: 263693

group them by [Product Code] and Description

   SELECT [Product Code], Description, Sum([Purchase Price]) AS Total
    FROM [products/stock] AS p 
     INNER JOIN [Stock Conversion Items] AS s 
     ON p.[Product Code] = s.[Result PC]
   WHERE s.CutID = [Enter your Cut ID here]
   GROUP BY [Product Code], Description

Upvotes: 1

Related Questions