The column " Goods.RetailPrice " is not valid in the select list, because it is not contained in either the aggregate function or the GROUP BY .

The column " Sales.Count " is not valid in the selection list, because it is not contained in either the aggregate function or the GROUP BY .

Here is the code:

 SELECT (Sum(Goods.RetailPrice * Sales.Count) * dbo.GetDiscount(Goods.RetailPrice * Sales.Count)/100) as Results FROM Sales, Goods WHERE Sales.NameID = Goods.id AND Sales.Date BETWEEN DATEADD(MM, -1, GETDATE()) AND GETDATE()

Why MS SQL swears at this code?

  • @ T2skler, If you are given an exhaustive answer, mark it as correct (click on the check mark next to the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

Because he does not know what you mean. I, for example, also do not understand: you multiply the amount of all documents for the period for a discount, determined for any one position. But for which of the many positions you consider a discount - that is still a mystery.

  • I need to determine the amount of the total discount on all sales for the month. How can I do that? GetDiscount returns INT - discount in percent. - T2skler
  • one
    (Sum (Goods.RetailPrice * Sales.Count) / 100. * Dbo.GetDiscount (Sum (Goods.RetailPrice * Sales.Count))) as Results - maxleo
  • And yes, it is not decent to talk about it, but I still say: within a month, the price could change. - maxleo
  • For a course, everything is fixed)) Thank you very much) But for some reason he does not think so, for example, from 14950 with a discount of 2 percent should write 299, and displays 319. Clearly, two goods were bought in the last month, one is 14950 and the second is 1000, he calculates 2% and applies this discount for both products. How to sum them separately? It is necessary for each transaction, sum up the discount separately. Those. in the first purchase comes out 2%, because > 10,000, and in the second 0% - T2skler
  • one
    (Sum (Goods.RetailPrice * Sales.Count / 100. * Dbo.GetDiscount (Goods.RetailPrice * Sales.Count))) as Results - maxleo