{"id":33010,"date":"2023-02-03T20:41:37","date_gmt":"2023-02-03T15:11:37","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/"},"modified":"2023-02-03T20:41:37","modified_gmt":"2023-02-03T15:11:37","slug":"solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/","title":{"rendered":"[Solved] How should I calculate the average speed by road segment for multiple segments?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-34795821\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"34795821\" data-parentid=\"34794664\" data-score=\"21\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>When averaging speeds, the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/en.wikipedia.org\/wiki\/Harmonic_mean\">harmonic mean<\/a> is in need.<\/p>\n<p>The straight forward <code>AVG()<\/code> approach is wrong, the arithmetic mean yields the wrong result for average velocity.<\/p>\n<p>There is no predefined function for the harmonic mean, but it could be achieved with this query:<\/p>\n<pre><code>SELECT segment,\n       COUNT(*)\/SUM(1e0\/speed) AS avg_speed\nFROM T \nGROUP BY segment\n<\/code><\/pre>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/9986d\/3\">SQL Fiddle<\/a><\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved How should I calculate the average speed by road segment for multiple segments? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] When averaging speeds, the harmonic mean is in need. The straight forward AVG() approach is wrong, the arithmetic mean yields the wrong result for average velocity. There is no predefined function for the harmonic mean, but it could be achieved with this query: SELECT segment, COUNT(*)\/SUM(1e0\/speed) AS avg_speed FROM T GROUP BY segment SQL &#8230; <a title=\"[Solved] How should I calculate the average speed by road segment for multiple segments?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\" aria-label=\"More on [Solved] How should I calculate the average speed by road segment for multiple segments?\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[1351,341,500],"class_list":["post-33010","post","type-post","status-publish","format-standard","hentry","category-solved","tag-mean","tag-sql","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] When averaging speeds, the harmonic mean is in need. The straight forward AVG() approach is wrong, the arithmetic mean yields the wrong result for average velocity. There is no predefined function for the harmonic mean, but it could be achieved with this query: SELECT segment, COUNT(*)\/SUM(1e0\/speed) AS avg_speed FROM T GROUP BY segment SQL ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-03T15:11:37+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How should I calculate the average speed by road segment for multiple segments?\",\"datePublished\":\"2023-02-03T15:11:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\"},\"wordCount\":74,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"mean\",\"sql\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\",\"name\":\"[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-02-03T15:11:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How should I calculate the average speed by road segment for multiple segments?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb","og_description":"[ad_1] When averaging speeds, the harmonic mean is in need. The straight forward AVG() approach is wrong, the arithmetic mean yields the wrong result for average velocity. There is no predefined function for the harmonic mean, but it could be achieved with this query: SELECT segment, COUNT(*)\/SUM(1e0\/speed) AS avg_speed FROM T GROUP BY segment SQL ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/","og_site_name":"JassWeb","article_published_time":"2023-02-03T15:11:37+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How should I calculate the average speed by road segment for multiple segments?","datePublished":"2023-02-03T15:11:37+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/"},"wordCount":74,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["mean","sql","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/","url":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/","name":"[Solved] How should I calculate the average speed by road segment for multiple segments? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-02-03T15:11:37+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-should-i-calculate-the-average-speed-by-road-segment-for-multiple-segments\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How should I calculate the average speed by road segment for multiple segments?"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/33010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=33010"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/33010\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=33010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=33010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=33010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}