{"id":17972,"date":"2022-10-28T08:23:03","date_gmt":"2022-10-28T02:53:03","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/"},"modified":"2022-10-28T08:23:03","modified_gmt":"2022-10-28T02:53:03","slug":"solved-mysql-query-0002-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/","title":{"rendered":"[Solved] Mysql Query [0002] [closed]"},"content":{"rendered":"

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

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

Assuming you want a figure of 0 for any month and \/ or city which doesn’t have any sales, but where there are sales for other cities for that month then something like this:-<\/p>\n

Cross join a pair of subselects, one to get a list of the months used and one to get a list of the cities, then join those against the records to get the amounts for that month \/ city, and sum those amounts up:-<\/p>\n

SELECT Sub1.YearMonth, Sub2.city, SUM(sales.amount)\nFROM (SELECT DISTINCT EXTRACT(YEAR_MONTH FROM `date`) AS YearMonth\nFROM sales) Sub1\nCROSS JOIN (SELECT DISTINCT city FROM users) Sub2\nLEFT OUTER JOIN sales ON Sub1.YearMonth = EXTRACT(YEAR_MONTH FROM sales.`date`) \nLEFT OUTER JOIN users ON sales.user_id = users.id AND Sub2.city = sales.city\nGROUP BY Sub1.YearMonth, Sub2.city\nORDER BY Sub1.YearMonth, Sub2.city\n<\/code><\/pre>\n
Down side of this is that if you have a month where nothing was sold to anybody then this month will not appear at all. To get around this you would need to change the subselect for the months to instead take a start date and add a range of numbers to it to get each month.<\/p>\n
An example of generating a range is as follows:-<\/p>\n
SELECT Sub1.YearMonth, Sub2.city, SUM(sales.amount)\nFROM (SELECT EXTRACT(YEAR_MONTH FROM DATE_ADD('2009-01-01', INTERVAL a.i*100+b.i*10+c.i MONTH)) AS aMonth\nFROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,\n(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,\n(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c\nWHERE DATE_ADD('2009-01-01', INTERVAL a.i*100+b.i*10+c.i MONTH) <=  '2014-12-01') Sub1\nCROSS JOIN (SELECT DISTINCT city FROM users) Sub2\nLEFT OUTER JOIN sales ON Sub1.YearMonth = EXTRACT(YEAR_MONTH FROM sales.`date`) \nLEFT OUTER JOIN users ON sales.user_id = users.id AND Sub2.city = sales.city\nGROUP BY Sub1.YearMonth, Sub2.city\nORDER BY Sub1.YearMonth, Sub2.city\n<\/code><\/pre>\n
This example is giving every month between 2009-01-01 and 2014-12-01 inclusive, then getting all the sales by city for that range. It will cope with a range of up to 1000 months.<\/p>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            <\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved Mysql Query [0002] [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Assuming you want a figure of 0 for any month and \/ or city which doesn’t have any sales, but where there are sales for other cities for that month then something like this:- Cross join a pair of subselects, one to get a list of the months used and one to get a … 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":[546,340,341],"class_list":["post-17972","post","type-post","status-publish","format-standard","hentry","category-solved","tag-database","tag-mysql","tag-sql"],"yoast_head":"\n[Solved] Mysql Query [0002] [closed] - 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-mysql-query-0002-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Mysql Query [0002] [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Assuming you want a figure of 0 for any month and \/ or city which doesn’t have any sales, but where there are sales for other cities for that month then something like this:- Cross join a pair of subselects, one to get a list of the months used and one to get a ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-28T02:53:03+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Mysql Query [0002] [closed]\",\"datePublished\":\"2022-10-28T02:53:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/\"},\"wordCount\":188,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"database\",\"mysql\",\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/\",\"name\":\"[Solved] Mysql Query [0002] [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-10-28T02:53:03+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-mysql-query-0002-closed\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Mysql Query [0002] [closed]\"}]},{\"@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\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Mysql Query [0002] [closed] - 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-mysql-query-0002-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Mysql Query [0002] [closed] - JassWeb","og_description":"[ad_1] Assuming you want a figure of 0 for any month and \/ or city which doesn’t have any sales, but where there are sales for other cities for that month then something like this:- Cross join a pair of subselects, one to get a list of the months used and one to get a ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/","og_site_name":"JassWeb","article_published_time":"2022-10-28T02:53:03+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Mysql Query [0002] [closed]","datePublished":"2022-10-28T02:53:03+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/"},"wordCount":188,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["database","mysql","sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/","name":"[Solved] Mysql Query [0002] [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-28T02:53:03+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-mysql-query-0002-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Mysql Query [0002] [closed]"}]},{"@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\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","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\/17972","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=17972"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/17972\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=17972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=17972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=17972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}