{"id":371,"date":"2024-01-31T16:22:05","date_gmt":"2024-01-31T10:22:05","guid":{"rendered":"http:\/\/itllive.com\/knowledgebase\/?p=371"},"modified":"2024-03-12T11:52:41","modified_gmt":"2024-03-12T05:52:41","slug":"some-useful-mysql-queries-for-ready-reference","status":"publish","type":"post","link":"https:\/\/itllive.com\/knowledgebase\/2024\/01\/31\/some-useful-mysql-queries-for-ready-reference\/","title":{"rendered":"Some useful MySQL queries for ready reference"},"content":{"rendered":"<p>Finding non-unique values in a column:<\/p>\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \r\n    <span class=\"hljs-keyword\">col_name<\/span>, \r\n    <span class=\"hljs-keyword\">COUNT<\/span>(<span class=\"hljs-keyword\">col_name<\/span>)\r\n<span class=\"hljs-keyword\">FROM<\/span>\r\n    table_name\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">col_name<\/span>\r\n<span class=\"hljs-keyword\">HAVING<\/span> <span class=\"hljs-keyword\">COUNT<\/span>(<span class=\"hljs-keyword\">col_name<\/span>) &gt; <span class=\"hljs-number\">1<\/span>;<\/code><\/pre>\n<p>Use of Case When:<\/p>\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\">SELECT \r\n\tcategory, \r\n\tSUM(CASE WHEN topic=\"stock_at_factory\" AND `range`=\"daily\" THEN qty_kg ELSE 0 END) AS stock_at_factory, \r\n\tSUM(CASE WHEN topic=\"stock_at_market\"  AND `range`=\"daily\" THEN qty_kg ELSE 0 END) AS stock_at_market\r\nFROM sku_summary \t\r\nWHERE \r\n\teff_date=\"2024-03-11\" \r\n\tAND entity=\"ITL\" \r\n  AND prod_line=\"TEA\" \r\nGROUP BY category\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Finding non-unique values in a column: SELECT col_name, COUNT(col_name) FROM table_name GROUP BY col_name HAVING COUNT(col_name) &gt; 1; ...<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[],"class_list":["post-371","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/371","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/comments?post=371"}],"version-history":[{"count":3,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/371\/revisions"}],"predecessor-version":[{"id":379,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/371\/revisions\/379"}],"wp:attachment":[{"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/media?parent=371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/categories?post=371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itllive.com\/knowledgebase\/wp-json\/wp\/v2\/tags?post=371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}