{"id":11706,"date":"2022-09-28T07:56:02","date_gmt":"2022-09-28T02:26:02","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/"},"modified":"2022-09-28T07:56:02","modified_gmt":"2022-09-28T02:26:02","slug":"solved-mysql-show-data-in-pivot-view","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/","title":{"rendered":"[Solved] Mysql show data in Pivot View"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-16013046\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"16013046\" data-parentid=\"16012098\" data-score=\"1\" 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>Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total number of rows for each user before this 5 day period:<\/p>\n<pre><code>select \n  coalesce(username, 'Grand Total') username,\n  max(`&lt; 5 days`) `&lt; 5 days`,\n  sum(case when maildate=\"6-Apr\" then 1 else 0 end) `6-Apr`,\n  sum(case when maildate=\"7-Apr\" then 1 else 0 end) `7-Apr`,\n  sum(case when maildate=\"8-Apr\" then 1 else 0 end) `8-Apr`,\n  sum(case when maildate=\"9-Apr\" then 1 else 0 end) `9-Apr`,\n  sum(case when maildate=\"10-Apr\" then 1 else 0 end) `10-Apr`,\n  count(*) GrandTotal\nfrom\n(\n  select c.username,\n    date_format(c.mailtime, '%e-%b') maildate,\n    coalesce(o.`&lt; 5 days`, 0) `&lt; 5 days`\n  from yt c\n  left join\n  (\n    select username,\n      count(*) `&lt; 5 days`\n    from yt\n    where mailtime &lt;= (select date_sub(max(mailtime), interval 4 DAY)\n                        from yt)\n  ) o\n    on c.username = o.username\n  where c.mailtime &gt;= (select date_sub(max(mailtime), interval 4 DAY)\n                          from yt)\n) d\ngroup by username with rollup;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!8\/71b7b\/32\">SQL Fiddle with Demo<\/a>.<\/p>\n<p>I wrote a hard-coded version so you could see how the code with be written but if you are basing the data off the <code>max(mailtime)<\/code> then you will most likely want to use dynamic SQL to get the result.  You can use a prepared statement to generate the SQL string that will be executed:<\/p>\n<pre><code>SET @sql = NULL;\nSELECT\n  GROUP_CONCAT(DISTINCT\n    CONCAT(\n      'sum(CASE WHEN maildate=\"\"',\n      date_format(mailtime, '%e-%b'),\n      ''' THEN 1 else 0 END) AS `',\n      date_format(mailtime, '%e-%b'), '`'\n    )\n  ) INTO @sql\nFROM yt\nWHERE mailtime &gt;= (select date_sub(max(mailtime), interval 4 DAY)\n                   from yt);\n\nSET @sql \n  = CONCAT('SELECT coalesce(username, ''Grand Total'') username,\n              max(`&lt; 5 days`) `&lt; 5 days`, ', @sql, ' ,\n              count(*) GrandTotal\n            from\n            (\n              select c.username,\n                date_format(c.mailtime, ''%e-%b'') maildate,\n                coalesce(o.`&lt; 5 days`, 0) `&lt; 5 days`\n              from yt c\n              left join\n              (\n                select username,\n                  count(*) `&lt; 5 days`\n                from yt\n                where mailtime &lt;= (select date_sub(max(mailtime), interval 4 DAY)\n                                    from yt)\n              ) o\n                on c.username = o.username\n              where c.mailtime &gt;= (select date_sub(max(mailtime), interval 4 DAY)\n                                      from yt)\n            ) d\n            group by username with rollup ');\n\n\nPREPARE stmt FROM @sql;\nEXECUTE stmt;\nDEALLOCATE PREPARE stmt;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!8\/71b7b\/40\">SQL Fiddle with Demo<\/a>. Both queries give the result:<\/p>\n<pre><code>|    USERNAME | &lt; 5 DAYS | 6-APR | 7-APR | 8-APR | 9-APR | 10-APR | GRANDTOTAL |\n--------------------------------------------------------------------------------\n|       User1 |        0 |     1 |     4 |     7 |     7 |      5 |         24 |\n|       User2 |        0 |     0 |     1 |     0 |     0 |      1 |          2 |\n|       User3 |        0 |     0 |     0 |     0 |     0 |      1 |          1 |\n|       User4 |        2 |     1 |     2 |     7 |     5 |      2 |         17 |\n|       User5 |        0 |     0 |     2 |     1 |     1 |      0 |          4 |\n| Grand Total |        2 |     2 |     9 |    15 |    13 |      9 |         48 |\n<\/code><\/pre>\n<p>If this not the result that you want, then you will have to further explain your need.<\/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 Mysql show data in Pivot View <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total &#8230; <a title=\"[Solved] Mysql show data in Pivot View\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\" aria-label=\"More on [Solved] Mysql show data in Pivot View\">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,3197,839,953],"class_list":["post-11706","post","type-post","status-publish","format-standard","hentry","category-solved","tag-mysql","tag-mysql-workbench","tag-pivot","tag-select"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Mysql show data in Pivot View - 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-show-data-in-pivot-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Mysql show data in Pivot View - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T02:26:02+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-show-data-in-pivot-view\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Mysql show data in Pivot View\",\"datePublished\":\"2022-09-28T02:26:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\"},\"wordCount\":164,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"mysql\",\"mysql-workbench\",\"pivot\",\"select\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\",\"name\":\"[Solved] Mysql show data in Pivot View - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-28T02:26:02+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Mysql show data in Pivot View\"}]},{\"@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=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"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 show data in Pivot View - 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-show-data-in-pivot-view\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Mysql show data in Pivot View - JassWeb","og_description":"[ad_1] Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/","og_site_name":"JassWeb","article_published_time":"2022-09-28T02:26:02+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-show-data-in-pivot-view\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Mysql show data in Pivot View","datePublished":"2022-09-28T02:26:02+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/"},"wordCount":164,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["mysql","mysql-workbench","pivot","select"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/","url":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/","name":"[Solved] Mysql show data in Pivot View - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-28T02:26:02+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-mysql-show-data-in-pivot-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Mysql show data in Pivot View"}]},{"@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=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","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\/11706","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=11706"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11706\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}