locked
Master-Detail group by sum query RRS feed

  • Question

  • User740553633 posted

    Hi, 

    For ease of explanation, i have the following 3 entities (tables) in a SQL Express DB,

    BAR CATEGORY (Beers, Spirits, etc)
    ------------
    ID
    Name


    BARTAKING (Daily capture of sales)

    --------
    ID
    TAKING_DATE
    BARMAN_ID


    BARTAKING_LINE (Daily amount captured per bar category)
    --------------
    ID
    BARTAKING_ID (fk)
    BARCATEGORY_ID (fk)
    AMOUNT

    I am battling to write a LINQ query to give me a the daily sales (per bar category) for the last three weeks.  I will specify the bar category id in the query, so I can run it for any of the bar categories.

    Can anybody assist me

    thanks

    Mel

    Saturday, January 9, 2016 6:28 PM

Answers

  • User-271186128 posted

    Hi Mel,

    Welcome to asp.net forum.

    As for this issue, you could use join clause and group by statement to join the table and get the result. You could refer to the following code:

                    DateTime endDT= DateTime.Now;
                    DateTime startDT  = DateTime.Now.AddDays(-15);
    
                    var queryList = (from bb in context.BARTAKING
                                     join bl in context.BARTAKING_LINE
                                     on bb.ID equals bl.BARTAKING_ID
                                     where bb.TAKING_DATE >= startDT && bb.TAKING_DATE <= endDT  //
                                     group bl by bl.BARCATEGORY_ID into newgroup
                                     select new
                                     {
                                         CATEGORYID = newgroup.Key,
                                         AMOUNT = newgroup.Sum(cc => cc.AMOUNT)
                                     }).ToList();

    More details about join clause and group by statement, please refer to the following articles:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx#SimpleInnerJoin

    https://msdn.microsoft.com/en-us/library/bb896250(v=vs.110).aspx

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2016 2:28 AM
  • User-271186128 posted

    Hi melvync,

    You could use OrderBy method to sort the result and then get the first record using FirstOrDefault() method.

    Like this:

                    var data = (from bt in db.BarTakings
    
                                where bt.TakingDate >= startDT
    
                                   && bt.TakingDate <= endDT
    
                                group bt by bt.TakingDate into dataGroup
    
                                select new TotalSalesMonthly()
    
                                {
    
                                    SalesDate = dataGroup.Key,
    
                                    TotalSales = dataGroup.Sum(c => c.DailyTotal)
    
                                }
    
                                ).OrderBy(c=>c.TotalSales).FirstOrDefault();
    

    Note, if you are using above code the data is a TotalSalesMonthly entity, not IEnumerable<TotalSalesMonthly>.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 19, 2016 2:18 AM

All replies

  • User-271186128 posted

    Hi Mel,

    Welcome to asp.net forum.

    As for this issue, you could use join clause and group by statement to join the table and get the result. You could refer to the following code:

                    DateTime endDT= DateTime.Now;
                    DateTime startDT  = DateTime.Now.AddDays(-15);
    
                    var queryList = (from bb in context.BARTAKING
                                     join bl in context.BARTAKING_LINE
                                     on bb.ID equals bl.BARTAKING_ID
                                     where bb.TAKING_DATE >= startDT && bb.TAKING_DATE <= endDT  //
                                     group bl by bl.BARCATEGORY_ID into newgroup
                                     select new
                                     {
                                         CATEGORYID = newgroup.Key,
                                         AMOUNT = newgroup.Sum(cc => cc.AMOUNT)
                                     }).ToList();

    More details about join clause and group by statement, please refer to the following articles:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx#SimpleInnerJoin

    https://msdn.microsoft.com/en-us/library/bb896250(v=vs.110).aspx

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2016 2:28 AM
  • User740553633 posted

    thanks Dillion,

    Will give it a try, and let you know

    Tuesday, January 12, 2016 5:26 PM
  • User740553633 posted

    This is what the data looks like,


    ID

    TakingDate

    Till Number

    MemberID

    DailyTotal

    21

    1/13/2016 12:00:00 AM

    1

    19

    300.00

    22

    1/13/2016 12:00:00 AM

    1

    19

    300.00

    23

    1/12/2016 12:00:00 AM

    1

    19

    1200.00

     

    This query,

     

    public IEnumerable<TotalSalesMonthly> TotalSalesMonthlyDict

            {

                get

                {

                    DateTime endDT = DateTime.Now;

                    DateTime startDT = DateTime.Now.AddDays(-21);

     

                    var data = (from bt in db.BarTakings

                                where bt.TakingDate >= startDT

                                   && bt.TakingDate <= endDT

                                group bt by bt.TakingDate into dataGroup

                                select new TotalSalesMonthly()

                                {

                                    SalesDate = dataGroup.Key,

                                    TotalSales = dataGroup.Sum(c => c.DailyTotal)

                                }

                                ).ToList();

     

                    return data;

                }

            }

    Produces the following output (which is correct), 

    2016-01-12   1200.00
    2016-01-13     600.00

    How can I get only the last record, i.e the 600.00?

    Friday, January 15, 2016 5:56 PM
  • User-271186128 posted

    Hi melvync,

    You could use OrderBy method to sort the result and then get the first record using FirstOrDefault() method.

    Like this:

                    var data = (from bt in db.BarTakings
    
                                where bt.TakingDate >= startDT
    
                                   && bt.TakingDate <= endDT
    
                                group bt by bt.TakingDate into dataGroup
    
                                select new TotalSalesMonthly()
    
                                {
    
                                    SalesDate = dataGroup.Key,
    
                                    TotalSales = dataGroup.Sum(c => c.DailyTotal)
    
                                }
    
                                ).OrderBy(c=>c.TotalSales).FirstOrDefault();
    

    Note, if you are using above code the data is a TotalSalesMonthly entity, not IEnumerable<TotalSalesMonthly>.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 19, 2016 2:18 AM
  • User740553633 posted

    thanks Dillion,

    I was sort of on the right track with using .First(), but just not quite there.  Your assistance is greatly appreciated.

    Wednesday, January 20, 2016 7:48 PM