SELECT COL1 as B1, COL2 as B2, SUM(CONVERT(Money, COL3)) as B3
FROM a-table
GROUP BY COL1, COL2
HAVING(COL1 < 40)
a-table:
COL1 COL2 COL3
11 "12" "13"
11 "12" "23"31 "32" "33"
31 "32" NULL
41 "42" "43"
Expected result:
B1 B2 B3
11 "12" 36
31 "32" 33
It took me quite a long time to figure out how to create such a query in LINQ using a List of objects as source.
Here is first result of my struggles:
using System.Linq.Dynamic;
. . .
var res =
list.AsQueryable().Where("COL1<40", null)
.GroupBy("new(COL1 as B1,COL2 as B2)", "it")
.Select("new(Key, Sum(COL3==null?0:double.Parse(COL3)) as B3)",
null);
The "res" gives two records:
[0] {Key={B1=11, B2=12}, B3=36}
[1] {Key={B1=31, B2=32}, B3=33}
Then I need to "flatten" this result to produce what I really needed (but this is another task, not shown here)
[0] {B1=11, B2=12, B3=36}
[1] {B1=31, B2=32, B3=33}
Please let me know if anyone has a better solution.
No comments:
Post a Comment