CODEWITHSUNDEEP
sqlsql-server
Kevin Lund
Kevin Lund

Reputation: 159

Error in SQL sum()

I have this SQL line

 SELECT No_, sum(Quantity) AS Sold, [Shipment Date] AS SoldDate, [Item Category Code],
 Description, [Description 2] FROM dbo.[3S Company A_S$Sales Invoice Line] 
 WHERE [Item Category Code] = '5104' GROUP BY No_

But i got this error for my script. 

Column 'dbo.3S Company A_S$Sales Invoice Line.Shipment Date' is invalid in the select
 list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone help me with why?

Upvotes: 0

Views: 2495

Answers (5)

Md.Rajibul Ahsan
Md.Rajibul Ahsan

Reputation: 853

TRY THIS

   SELECT No_, sum(Quantity) AS Sold, [Shipment Date] AS SoldDate, [Item Category Code],
   Description, [Description 2] FROM dbo.[3S Company A_S$Sales Invoice Line] 
   WHERE [Item Category Code] = '5104' GROUP BY No_,[Shipment Date],
   [ItemCategoryCode], Description,[Description 2]

IN SQL IF YOU USE ANY COLUMN NAME IN SELECT CLAUSE EXCEPT AGGREGRATE FUNCTION THEN YOU NEED TO ADD ALL THE COLUMNS IN GROUP BY ALSO OTHER WISE IT WILL SHOW EXCEPTION

IF YOU WANT SUM ONLY BY NO_ Column then you have to write a subquery with the aggregarte function and join it to you other columns as folows

     SELECT No_ ,quant.sold, [Shipment Date] AS SoldDate, [Item Category Code],
   Description, [Description 2] FROM dbo.[3S Company A_S$Sales Invoice Line] INV,
  (SELECT No_, sum(Quantity) AS Sold from dbo.[3S Company A_S$Sales Invoice Line] where 
    WHERE [Item Category Code] = '5104' group by No_) quant


   WHERE [Item Category Code] = '5104' and 
   inv.no_=quant.no_

Upvotes: 0

Ryx5
Ryx5

Reputation: 1366

You have to put columns not using the aggregate functions in your GROUP BY :

GROUP BY 
    No_
    , [Shipment Date]
    , [Item Category Code]
    , Description
    , [Description 2]

Upvotes: 0

Sivaraman
Sivaraman

Reputation: 458

The columns (Other than aggregate functions) that exists in select clause should also present in the group by clause. This is what the error message states.

Select Productid, Sum(Saled) from product 
Group by ProductId

In the above example, ProductId is in the group by clause. So that query is valid. If you introduce one more column that should also be in the group by clause to avoid error.

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

The error means you have a column which may have multiple values when grouped and SQL doesn't know which value to select in the column

You cn use e.g. min() to select min value. Like this

 SELECT No_, 
        sum(Quantity) AS Sold, 
        min([Shipment Date]) AS SoldDate, 
        min([Item Category Code]),
        min(Description), 
        min([Description 2]) 
 FROM dbo.[3S Company A_S$Sales Invoice Line] 
 WHERE [Item Category Code] = '5104' 
 GROUP BY No_

Or read about aggregate functions to choose proper one

BWT it's not MySQL but rather MS SQL (MySQL does not complain the column usages)

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

If you use GROUP BY in your query, only the columns used in your grouping clause and any aggregate functions like SUM are allowed in the select list. In your case, you specify GROUP BY No_, so that is the only column you can select without using an aggregate function.

If you want to get the remaining columns, you could select No_ and the other aggregate columns in a subquery and then select other columns by matching the No_ column with the corresponding column in subquery.

Upvotes: 1

Related Questions