sql linq, sum a column in a select new with
-
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
SELECT
coH.PartNumber
, coH.ManPartNumber
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPricethere must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
{
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price, -
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
SELECT
coH.PartNumber
, coH.ManPartNumber
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPricethere must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
{
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
-
Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
This is probably the result of using EF against one of the desktop versions of a database, don't support stored procs. It is also the way of the new developer, use linq for everything. TSQL is not considered and not learnt because they can just wire up EF. But then I'm old school :sigh:
Never underestimate the power of human stupidity RAH
-
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
SELECT
coH.PartNumber
, coH.ManPartNumber
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPricethere must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
{
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,You need to group the set. Have a look at this[^] example.
Wrong is evil and must be defeated. - Jeff Ello
-
You need to group the set. Have a look at this[^] example.
Wrong is evil and must be defeated. - Jeff Ello
Thanks! I got thtis to work, but I can't figure out how to insert my 2 joins, and get those values all in 1 result set
Dim pResults = _
(
From cohc In context.Order_History_Cart
Where cohc.PartNumber = "24-FLP44"
Group By cohc = New With
{
cohc.Qty,
cohc.Cost,
cohc.Price
} Into group
Select New With
{
.totalQty = group.Sum(function(q) q.Qty),
.totalCost = group.Sum(function(c) c.Qty * c.Cost),
.TotalPrice = group.Sum(function(p) p.Qty * p.Price)
}
)