LINQ- group by month, year and return a sum

Question

0.00/5 (No votes)

See more:

Hi,

I am new to Linq, traditionally from a SQL background. I am trying to adapt to Linq, but the syntax seems complex and I don't know whether there is any benefit.

Anyway...

How would i write the below in LInq?

Thanks for your help

What I have tried:

I have table with a total and a date -using SQL I would have used,

select year(Deliverydate) as yr, month(Deliverydate) as mnth, sum(TotalCharge) as tCharge
from MediaSale
group by year(Deliverydate), month(Deliverydate)

which returns the below result:

which returns the below result:

2017	6	472.2500
2017	7	2115.7700

Posted 30-Jul-17 12:42pm

Member 10583196

Updated 2-Apr-22 19:21pm

Add a Solution

Comments

Graeme_Grant 30-Jul-17 19:47pm

There is not enough in your question to work with.

Is this a single table query or are there multiple tables involved? Do you have a slice of sample data to be queried?

Click on "Improve question" to update your question. If you are not sure how this will show you: https://www.codeproject.com/Articles/64628/Code-Project-Quick-Answers-FAQ#improve

Member 10583196 30-Jul-17 20:08pm

Apologies I thought the SQL query would be enough information.

This is a single table query as defined by "From MediaSale"

There is a date field within the data - "DeliveryDate"

And a field named "TotalCharge" which I want to sum by month/year.

Table definition is as below:

CREATE TABLE [dbo].[MediaSale] (
    [MediaSaleID]         INT             IDENTITY (1, 1) NOT NULL,
    [DeliveryDate]        DATETIME        NOT NULL,
    [MediaSaleCustomerID] INT             NOT NULL,
    [InternalOrderNumber]  NVARCHAR (MAX)  NULL,
    [CustomerOrderNumber] NVARCHAR (MAX)  NULL,
    [RecordCount]         INT             NOT NULL,
    [FileCount]           INT             NOT NULL,
    [SelectionDetail]     NVARCHAR (MAX)  NULL,
    [ProcessingCharge]    DECIMAL (19, 4) NULL,
    [DeliveryCharge]      DECIMAL (19, 4) NULL,
    [TotalCharge]         DECIMAL (19, 4) NULL,
    CONSTRAINT [PK_MediaSales] PRIMARY KEY CLUSTERED ([MediaSaleID] ASC),
    CONSTRAINT [FK_MediaSales_MSC] FOREIGN KEY ([MediaSaleCustomerID]) REFERENCES [dbo].[MediaSaleCustomer] ([MediaSaleCustomerID])
);

Graeme_Grant 30-Jul-17 21:05pm

Click on "Improve question" to update your question. If you are not sure how this will show you: https://www.codeproject.com/Articles/64628/Code-Project-Quick-Answers-FAQ#improve

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-07-30T19:26:00

Solution 2

try

List<Entity> lst = new List<Entity>();
 var data = lst.Select(k => new { k.Deliverydate.Year, k.Deliverydate.Month, k.TotalCharge }).GroupBy(x => new { x.Year, x.Month }, (key, group) => new
{
    yr = key.Year,
    mnth = key.Month,
    tCharge = group.Sum(k => k.TotalCharge)
}).ToList();

Posted 30-Jul-17 19:26pm

Karthik_Mahalingam

Comments

Maciej Los 31-Jul-17 16:33pm

5ed!

Karthik_Mahalingam 1-Aug-17 3:49am

Thanks Maciej

Member 13206398 3-Apr-22 1:22am

Thank you

LINQ- group by month, year and return a sum

1 solution

Solution 2

Add your solution here

Preview 0

LINQ- group by month, year and return a sum

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us