Ok, everybody know that Sql group by construct is VERY limited, or at least compared with VFP implementation. With this:
Code Name Price Any
1 One 5 1
1 One 10 2
2 Two 1 3
2 Two 1 4
(hey, i know that is not a "rigth" table, but i put this for a simply idea, ok?, i don't need know how model rigth this example, i only need a solution for my question...so focus in the question and not in the data this time ;))
So, in VFP if i know the total of price for each product easy:
SELECT Code,Name,Any,Sum(Price) FROM Products GROUP By Code ORDER BY Code
Result:
1 One 15 1 = i put this fake data here to force the sql problem, i know that the result is not rigth
1 Two 2 3
But in sql server is impossible because force to put ALL fields in the group by, like
SELECT Code,Name,Any,Sum(Price) FROM Products GROUP By Code,Name,Any ORDER BY Code
And obvious this fail because is impossible group by all fields...and i need group only by one
So, i have this "solution"
select DISTINCT Code,Name,Any
(
select sum(Price) from Products
where Code=C1.Code) as Suma
from Products as C1
now, i have a correct sum for each product but this return multiple rows, i need only 1row*each code...
How solve this (i don't can use Sql user functions, only sql)
The Life is Beautiful!
Programmer in
Delphi, VS.NET
MCP