This post assumes you\u2019ve read the previous posts in this series:<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics<\/a><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT<\/a><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators<\/a><\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions<\/a><\/p>\n This post will discuss the GROUP BY and HAVING clauses.<\/p>\n GROUP BY<\/b><\/p>\n A SQL GROUP BY clause allows you to group records by particular fields, so the entire group can be dealt with at once.\u00a0 A LINQ statement can have a Group By clause as well, but with different syntax.\u00a0 An informal (and incomplete) syntax expression could be:<\/p>\n
| \n VB Group By<\/b><\/p>\n<\/td>\n<\/tr>\n Group [{optional list of fields}] By {list of fields} _<\/p>\n Into {list of aggregate expressions}<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n In this syntax, all the listed expressions can have aliases, and must if an identifier cannot be inferred.\u00a0 Essentially, the clause is \u201cGroup By\u201d followed by a list of fields by which to group the records, then \u201cInto\u201d followed by a list of aggregate expressions to calculate.\u00a0 For example, the following query calculates the total and average cost of shipments to each zip code:<\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group By Shipment.ShippingZip _<\/p>\n Into Total = Sum(Shipment.Cost), Average(Shipment.Cost)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n This query expression returns three values, ShippingZip, Total and Average, for each zip code represented in the data.<\/p>\n Optionally, a list of fields can be provided between the \u201cGroup\u201d and \u201cBy\u201d keywords to narrow down the information included to specific fields.\u00a0 This could be thought of as a built-in Select clause prior to the Into clause:<\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group OrderCost = Shipment.Cost By Shipment.ShippingZip _<\/p>\n Into Total = Sum(OrderCost), Average(OrderCost)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n In addition to aggregate expressions, the Into clause can also contain the keyword \u201cGroup\u201d, which causes the individual records to be included for each group, as an array member.\u00a0 For each pairing of zip code and order date represented in the data, the following query expression returns two values, Zip and OrderDate, plus an array representing all the records in the group.<\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group By Zip = Shipment.ShippingZip, Shipment.OrderDate _<\/p>\n Into Group<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n When there are no fields specified between \u201cGroup\u201d and \u201cOrder By\u201d, as above, all fields are included in the records in the Group array.\u00a0 Including specific fields narrows the information to the specified fields, as though the records were filtered through a Select clause.\u00a0 For each zip code and order date pairing represented in the data, the following query expression returns Zip and OrderDate values, plus an array of ID and Cost fields for each record in the group.<\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group ID = Shipment.OrderID, Shipment.Cost By _<\/p>\n \u00a0\u00a0\u00a0 Zip = Shipment.ShippingZip, Shipment.OrderDate _<\/p>\n Into Group<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n I\u2019ve given a lot of examples because the syntax is fairly complex, but now we start to see how to convert various SQL statements with GROUP BY clauses to VB.\u00a0 Below is an\u00a0example:<\/p>\n SQL<\/b><\/p>\n<\/td>\n<\/tr>\n SELECT OrderDate Date_Of_Order, SUM(Cost) Daily_Total<\/p>\n FROM OrderTable<\/p>\n GROUP BY Date_Of_Order<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group By Date_Of_Order = Shipment.OrderDate _<\/p>\n Into Daily_Total = Sum(Shipment.Cost)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n Having<\/i><\/b><\/b><\/p>\n Having is another SQL clause which can specify conditions for a group\u2019s inclusion in the query results.\u00a0 VB has no corresponding clause, so the best way to re-create this with VB LINQ is to use a Where clause after a Group By clause, as shown below.<\/p>\n SQL<\/b><\/p>\n<\/td>\n<\/tr>\n SELECT OrderDate Date_Of_Order, SUM(Cost) Total_Cost<\/p>\n FROM OrderTable<\/p>\n GROUP BY Date_Of_Order<\/p>\n HAVING SUM(Cost) > 1000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n VB<\/b><\/p>\n<\/td>\n<\/tr>\n From Shipment In OrderTable _<\/p>\n Group By Date_Of_Order = Shipment.OrderDate _<\/p>\n Into Total_Cost = Sum(Shipment.Cost)<\/p>\n Where Total_Cost > 1000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n In my next post, I plan to cover various kinds of joins.<\/p>\n – Bill Horst, VB IDE Test<\/p>\n","protected":false},"excerpt":{"rendered":" This post assumes you\u2019ve read the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions This post will discuss […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20,195],"tags":[41,49,94,166],"class_list":["post-4923","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converting-sql-to-linq","category-visual-basic","tag-bill-horst","tag-converting-sql-to-linq","tag-linqvb9","tag-vb2008"],"acf":[],"blog_post_summary":" This post assumes you\u2019ve read the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions This post will discuss […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4923"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4923\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |