{"id":18494,"date":"2022-11-01T01:31:01","date_gmt":"2022-10-31T20:01:01","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/"},"modified":"2022-11-01T01:31:01","modified_gmt":"2022-10-31T20:01:01","slug":"solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/","title":{"rendered":"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

The OP probably already knows this, but here is how to get the answer, disregarding efficiency. First, cards per location, as described in the comments:<\/p>\n

SELECT locationid, COUNT(DISTINCT cardID)\nFROM table \nGROUP BY locationid \n<\/code><\/pre>\n
Next the same thing per state.<\/p>\n
SELECT substring(locationid, 1, 2) state, COUNT(DISTINCT cardID)\nFROM table \nGROUP BY substring(locationid, 1, 2)\n<\/code><\/pre>\n
For a single state, <\/p>\n
select COUNT(DISTINCT cardID)\nfrom table \nwhere substring(locationid, 1, 2) = 'NY'\n<\/code><\/pre>\n
The problem with the first query is that it will return locationID’s like NY_1234.  If you have not memorized what NY_1234 is in layman’s terms, Rochester for example, you have to go outside your database to see what it is.<\/p>\n
The second query will be inefficient because it has to apply the substring function on a large number of records.  The third will be inefficient because it will have to scan the entire table.<\/p>\n
If you had a relational model you could do stuff like this.<\/p>\n
select municipality, count(distinct cardID)\nfrom table join location on table.locationid = location.locationID\ngroup by municipality\n\nselect stateCode, count(distinct cardID)\nfrom table join location on table.locationid = location.locationID\ngroup by stateCode\n<\/code><\/pre>\n
State codes would be NY, NJ, etc  If you wanted New York, New Jersey, etc, that would simply be another field.<\/p>\n
Finally, <\/p>\n
select count(distinct cardID)\nfrom table join location on table.locationid = location.locationID\nwhere stateCode=\"NY\"\n<\/code><\/pre>\n
The second two relational queries would be faster than the single table queries because they wouldn’t have to use functions.  You could speed it up even more with an index on stateCode, but even if you didn’t, a much smaller table would be scanned.<\/p>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            1<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved get statistics information by SQL query efficiently for table with 3 columns and 800 million rows <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] The OP probably already knows this, but here is how to get the answer, disregarding efficiency. First, cards per location, as described in the comments: SELECT locationid, COUNT(DISTINCT cardID) FROM table GROUP BY locationid Next the same thing per state. SELECT substring(locationid, 1, 2) state, COUNT(DISTINCT cardID) FROM table GROUP BY substring(locationid, 1, 2) … 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":[340,341,500,1685],"class_list":["post-18494","post","type-post","status-publish","format-standard","hentry","category-solved","tag-mysql","tag-sql","tag-sql-server","tag-windows-7"],"yoast_head":"\n[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - 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-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] The OP probably already knows this, but here is how to get the answer, disregarding efficiency. First, cards per location, as described in the comments: SELECT locationid, COUNT(DISTINCT cardID) FROM table GROUP BY locationid Next the same thing per state. SELECT substring(locationid, 1, 2) state, COUNT(DISTINCT cardID) FROM table GROUP BY substring(locationid, 1, 2) ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-31T20:01:01+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\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows\",\"datePublished\":\"2022-10-31T20:01:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\"},\"wordCount\":223,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"mysql\",\"sql\",\"sql-server\",\"windows-7\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\",\"name\":\"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-31T20:01:01+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows\"}]},{\"@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] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - 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-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - JassWeb","og_description":"[ad_1] The OP probably already knows this, but here is how to get the answer, disregarding efficiency. First, cards per location, as described in the comments: SELECT locationid, COUNT(DISTINCT cardID) FROM table GROUP BY locationid Next the same thing per state. SELECT substring(locationid, 1, 2) state, COUNT(DISTINCT cardID) FROM table GROUP BY substring(locationid, 1, 2) ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/","og_site_name":"JassWeb","article_published_time":"2022-10-31T20:01:01+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows","datePublished":"2022-10-31T20:01:01+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/"},"wordCount":223,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["mysql","sql","sql-server","windows-7"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/","url":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/","name":"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-31T20:01:01+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-get-statistics-information-by-sql-query-efficiently-for-table-with-3-columns-and-800-million-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows"}]},{"@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\/18494","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=18494"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18494\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=18494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=18494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=18494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}