LINQ to SQL with Totals

Ever since I have been using Linq2SQL, I am always impressed by the speed and the functionality of it. However; using SQL for 10+ years I always find my self challenged by converting SQL to
LINQ2SQL. The simple select statements and left, right, inner joins are pretty straight forward but what about something more complicated. For example.

Suppose I have two tables (Customers and Sales). I want to know how many orders there has been by each customer. For example:

[code language=”sql”]
id int primary key,
Customer varchar(50)

(1001,’Fred Flinstone’),
(1002,’Barney Rubble’),
(1003,’Pebbles Flinstone’),
(1004,’Betty Rubble’),
(1005,’Bamm-Bamm Rubble’),
(1006,’Mr. Slate’),
(1007,’Sam Slagheap’);

(custid int,
ordernum int,
amount decimal(9,2));

Insert Into Sales
(Custid, ordernum, amount)

View on SQL fiddle

Result I want is

1001 Fred Flinstone 3
1002 Barney Rubble 4
1003 Pebbles Flinstone 2
1004 Betty Rubble 0
1005 Bamm-Bamm Rubble 1
1006 Mr. Slate 1
1007 Sam Slagheap 4

SELECT cust.*, ss.OrderCount FROM Customers cust
SELECT count(custid) AS OrderCount, Custid FROM Sales GROUP BY Custid) ss ON = ss.Custid

From cs In Customers
Group Join rr In (
(From Sales In Sales
Group Sales By Sales.CustID Into g = Group
OrderCount = CType(g.Count(),Int64?))
) On New With { .CustID = cs.CustID } Equals New With { .CustID = CInt(rr.CustID) } Into rr_join = Group
From rr In rr_join.DefaultIfEmpty()
CustID = CType(cs.CustID,Int32?),
Column1 = If(CType(rr.OrderCount,Int64?) is Nothing,0,rr.OrderCount)

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *