SQL Query to Linq (Group By and Sum)

Discussion in 'ASP .Net' started by Alex Sauceda, Feb 4, 2008.

  1. Alex Sauceda

    Alex Sauceda Guest

    Hi Everyone,

    I'm trying to convert this query into LINQ but I don't have idea how to do
    it.

    Select C.sName as CustomerName, S.sName as Salesperson,
    Sum(O.nQty) as Qty, Sum(O.mAmount) as Amount
    From tOrders O
    Inner Join tCustomer C On C.CustomerID = O.CustomerID
    Inner Join tSalesperson S On S.SalespersonID = O.SalespersonID
    Group By C.sName, S.sName

    I Hope you can help me.

    Regards,

    Alex
     
    Alex Sauceda, Feb 4, 2008
    #1
    1. Advertising

  2. Alex Sauceda

    Marc Gravell Guest

    Well, it is hard to test without more info (perhaps something similar
    on "pubs" or "northwind"?) - but perhaps something along the lines of:

    var query = from order in db.Orders
    group order by new { Customer = order.Customer,
    SalesPerson = order.SalesPerson }
    into grp
    select new
    {
    Customer = grp.Key.Customer.Name,
    SalesPerson = grp.Key.SalesPerson.Name,
    Quantity = grp.Sum(o => o.Quantity),
    Amount = grp.Sum(o => o.Amount)
    };

    foreach (var item in query)
    {
    Console.WriteLine("{0}, {1}, {2}, {3}", item.SalesPerson,
    item.Customer, item.Amount, item.Quantity);
    }
     
    Marc Gravell, Feb 4, 2008
    #2
    1. Advertising

  3. Alex Sauceda

    Alex Sauceda Guest

    Using Northwind it would be something like this:
    Select C.CompanyName, O.ShipCity, Sum(D.Quantity) as Quantity,
    Sum(D.Quantity*D.UnitPrice) as Amount
    From Customers C Inner Join
    Orders O On C.CustomerID = O.CustomerID Inner Join
    [Order Details] D on O.OrderID = D.OrderID
    Group by C.CompanyName, O.ShipCity
    Order by C.CompanyName, O.ShipCity


    Thanks a lot for helping me.


    "Marc Gravell" <> wrote in message
    news:...
    > Well, it is hard to test without more info (perhaps something similar
    > on "pubs" or "northwind"?) - but perhaps something along the lines of:
    >
    > var query = from order in db.Orders
    > group order by new { Customer = order.Customer,
    > SalesPerson = order.SalesPerson }
    > into grp
    > select new
    > {
    > Customer = grp.Key.Customer.Name,
    > SalesPerson = grp.Key.SalesPerson.Name,
    > Quantity = grp.Sum(o => o.Quantity),
    > Amount = grp.Sum(o => o.Amount)
    > };
    >
    > foreach (var item in query)
    > {
    > Console.WriteLine("{0}, {1}, {2}, {3}", item.SalesPerson,
    > item.Customer, item.Amount, item.Quantity);
    > }
     
    Alex Sauceda, Feb 4, 2008
    #3
  4. Alex Sauceda

    Alex Sauceda Guest

    Your sample helped me a lot.

    Thanks a lot Marc.

    Regards,

    Alex

    "Marc Gravell" <> wrote in message
    news:...
    > Well, it is hard to test without more info (perhaps something similar
    > on "pubs" or "northwind"?) - but perhaps something along the lines of:
    >
    > var query = from order in db.Orders
    > group order by new { Customer = order.Customer,
    > SalesPerson = order.SalesPerson }
    > into grp
    > select new
    > {
    > Customer = grp.Key.Customer.Name,
    > SalesPerson = grp.Key.SalesPerson.Name,
    > Quantity = grp.Sum(o => o.Quantity),
    > Amount = grp.Sum(o => o.Amount)
    > };
    >
    > foreach (var item in query)
    > {
    > Console.WriteLine("{0}, {1}, {2}, {3}", item.SalesPerson,
    > item.Customer, item.Amount, item.Quantity);
    > }
     
    Alex Sauceda, Feb 4, 2008
    #4
  5. Alex Sauceda

    Marc Gravell Guest

    > Your sample helped me a lot.

    No problem; actually, it occurred to me that do get something close to
    your original, you should probably group directly on the names, i.e.

    group order by new { CustomerName = order.Customer.Name,
    SalesPersonName = order.SalesPerson.Name }
    into grp
    select new
    {
    grp.Key.CustomerName,
    grp.Key.SalesPersonName,
    <etc as before>

    For reference, you can inspect the generated SQL on a data-context via
    GetCommand(query); or use a SQL trace.

    Glad it helped, though ;-p
     
    Marc Gravell, Feb 4, 2008
    #5
  6. Alex Sauceda

    Alex Sauceda Guest

    SQL Trace helped me a lot.

    Thanks a again for answering.

    Regards,

    Alex

    "Marc Gravell" <> wrote in message
    news:...
    >> Your sample helped me a lot.

    >
    > No problem; actually, it occurred to me that do get something close to
    > your original, you should probably group directly on the names, i.e.
    >
    > group order by new { CustomerName = order.Customer.Name,
    > SalesPersonName = order.SalesPerson.Name }
    > into grp
    > select new
    > {
    > grp.Key.CustomerName,
    > grp.Key.SalesPersonName,
    > <etc as before>
    >
    > For reference, you can inspect the generated SQL on a data-context via
    > GetCommand(query); or use a SQL trace.
    >
    > Glad it helped, though ;-p
     
    Alex Sauceda, Feb 4, 2008
    #6
  7. Alex Sauceda

    Alex Sauceda Guest

    SQL Trace helped me a lot.

    Thanks again for answering.

    Regards,

    Alex

    "Marc Gravell" <> wrote in message
    news:...
    >> Your sample helped me a lot.

    >
    > No problem; actually, it occurred to me that do get something close to
    > your original, you should probably group directly on the names, i.e.
    >
    > group order by new { CustomerName = order.Customer.Name,
    > SalesPersonName = order.SalesPerson.Name }
    > into grp
    > select new
    > {
    > grp.Key.CustomerName,
    > grp.Key.SalesPersonName,
    > <etc as before>
    >
    > For reference, you can inspect the generated SQL on a data-context via
    > GetCommand(query); or use a SQL trace.
    >
    > Glad it helped, though ;-p
     
    Alex Sauceda, Feb 4, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonymous
    Replies:
    0
    Views:
    1,546
    Anonymous
    Oct 13, 2005
  2. Mike Gleason jr Couturier

    LINQ to SQL and new MS SQL 2008 datatypes

    Mike Gleason jr Couturier, Oct 28, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    329
    Cowboy \(Gregory A. Beamer\)
    Oct 29, 2008
  3. George

    Linq or not Linq

    George, Nov 4, 2008, in forum: ASP .Net
    Replies:
    4
    Views:
    384
    Mike Gleason jr Couturier
    Nov 5, 2008
  4. Craig Buchanan

    DetailsView and foreign key and LINQ-to-SQL

    Craig Buchanan, Jan 16, 2009, in forum: ASP .Net
    Replies:
    0
    Views:
    1,718
    Craig Buchanan
    Jan 16, 2009
  5. Andy B.

    Can't run linq to sql query?

    Andy B., Sep 8, 2009, in forum: ASP .Net
    Replies:
    4
    Views:
    1,289
    Andy B.
    Sep 8, 2009
Loading...

Share This Page