{"id":24751,"date":"2022-12-05T13:00:42","date_gmt":"2022-12-05T07:30:42","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/"},"modified":"2022-12-05T13:00:42","modified_gmt":"2022-12-05T07:30:42","slug":"solved-whats-wrong-with-my-sql","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/","title":{"rendered":"[Solved] Whats wrong with my SQL?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-32223696\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"32223696\" data-parentid=\"32223406\" 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 last query<\/p>\n<pre><code>   SELECT \n         0,\n         department,\n         department_name,\n         '',\n          'NP' as acc_code,\n         'NET PROFIT\/(LOSS)' as acc_name,1,\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*yr_balance ELSE yr_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*ly_balance ELSE ly_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\"  THEN -1*mn_balance ELSE mn_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\"  THEN -1*lm_balance ELSE lm_balance END)),\n         'CAT_NETPROFIT' as rsl_type,\n         1 \n   FROM \n     #tmp_rsl WHERE rsl_type=\"CAT_HEAD\"\n<\/code><\/pre>\n<p>is the problem. You use columns like &#8216;department&#8217; without any aggregate and then you have columns computed by an aggregate (like sum). You can not mix those colums. Either you have no aggregates, all aggregates or the columns without any aggregate must be part of your grouping, like this:<\/p>\n<pre><code>   SELECT \n         0,\n         department,\n         department_name,\n         '',\n          'NP' as acc_code,\n         'NET PROFIT\/(LOSS)' as acc_name,1,\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*yr_balance ELSE yr_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*ly_balance ELSE ly_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\"  THEN -1*mn_balance ELSE mn_balance END)),\n         SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\"  THEN -1*lm_balance ELSE lm_balance END)),\n         'CAT_NETPROFIT' as rsl_type,\n         1 \n   FROM \n     #tmp_rsl WHERE rsl_type=\"CAT_HEAD\"\n   GROUP BY department, department_name\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">0<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Whats wrong with my SQL? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Your last query SELECT 0, department, department_name, &#8221;, &#8216;NP&#8217; as acc_code, &#8216;NET PROFIT\/(LOSS)&#8217; as acc_name,1, SUM((CASE WHEN acc_code=&#8221;CS&#8221; OR acc_code=&#8221;EX&#8221; THEN -1*yr_balance ELSE yr_balance END)), SUM((CASE WHEN acc_code=&#8221;CS&#8221; OR acc_code=&#8221;EX&#8221; THEN -1*ly_balance ELSE ly_balance END)), SUM((CASE WHEN acc_code=&#8221;CS&#8221; OR acc_code=&#8221;EX&#8221; THEN -1*mn_balance ELSE mn_balance END)), SUM((CASE WHEN acc_code=&#8221;CS&#8221; OR acc_code=&#8221;EX&#8221; THEN -1*lm_balance ELSE lm_balance &#8230; <a title=\"[Solved] Whats wrong with my SQL?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/\" aria-label=\"More on [Solved] Whats wrong with my SQL?\">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":[341,500,4328],"class_list":["post-24751","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server","tag-ssms"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] Whats wrong with my SQL? - 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-whats-wrong-with-my-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Whats wrong with my SQL? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Your last query SELECT 0, department, department_name, &#039;&#039;, &#039;NP&#039; as acc_code, &#039;NET PROFIT\/(LOSS)&#039; as acc_name,1, SUM((CASE WHEN acc_code=&quot;CS&quot; OR acc_code=&quot;EX&quot; THEN -1*yr_balance ELSE yr_balance END)), SUM((CASE WHEN acc_code=&quot;CS&quot; OR acc_code=&quot;EX&quot; THEN -1*ly_balance ELSE ly_balance END)), SUM((CASE WHEN acc_code=&quot;CS&quot; OR acc_code=&quot;EX&quot; THEN -1*mn_balance ELSE mn_balance END)), SUM((CASE WHEN acc_code=&quot;CS&quot; OR acc_code=&quot;EX&quot; THEN -1*lm_balance ELSE lm_balance ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-05T07:30:42+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-whats-wrong-with-my-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Whats wrong with my SQL?\",\"datePublished\":\"2022-12-05T07:30:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/\"},\"wordCount\":66,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"sql\",\"sql-server\",\"ssms\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/\",\"name\":\"[Solved] Whats wrong with my SQL? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-12-05T07:30:42+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-whats-wrong-with-my-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Whats wrong with my SQL?\"}]},{\"@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] Whats wrong with my SQL? - 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-whats-wrong-with-my-sql\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Whats wrong with my SQL? - JassWeb","og_description":"[ad_1] Your last query SELECT 0, department, department_name, '', 'NP' as acc_code, 'NET PROFIT\/(LOSS)' as acc_name,1, SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*yr_balance ELSE yr_balance END)), SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*ly_balance ELSE ly_balance END)), SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*mn_balance ELSE mn_balance END)), SUM((CASE WHEN acc_code=\"CS\" OR acc_code=\"EX\" THEN -1*lm_balance ELSE lm_balance ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/","og_site_name":"JassWeb","article_published_time":"2022-12-05T07:30:42+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-whats-wrong-with-my-sql\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Whats wrong with my SQL?","datePublished":"2022-12-05T07:30:42+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/"},"wordCount":66,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server","ssms"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/","url":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/","name":"[Solved] Whats wrong with my SQL? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-05T07:30:42+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-whats-wrong-with-my-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Whats wrong with my SQL?"}]},{"@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\/24751","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=24751"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/24751\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=24751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=24751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=24751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}