{"id":4208,"date":"2024-10-02T09:48:12","date_gmt":"2024-10-02T01:48:12","guid":{"rendered":"https:\/\/five.co\/?p=4208"},"modified":"2026-05-04T16:39:03","modified_gmt":"2026-05-04T08:39:03","slug":"sql-case-when-count","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-case-when-count\/","title":{"rendered":"SQL CASE WHEN COUNT: Practical Guide with Real-World Examples"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL CASE WHEN COUNT<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL&#8217;s <code>CASE WHEN<\/code> statement combined with the <code>COUNT<\/code> function is a great for data analysis. This combo lets you create custom categories and count occurrences within those categories in a single query. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;ve ever found yourself running multiple queries to get counts for different conditions, this technique will save you time and simplify your code.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-qnu92 wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-qnu92 \"><div class=\"eb-toc-container eb-toc-qnu92  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL CASE WHEN COUNT&quot;,&quot;text&quot;:&quot;Breakdown of SQL CASE WHEN COUNT&quot;,&quot;link&quot;:&quot;breakdown-of-sql-case-when-count&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Why Use CASE WHEN with COUNT?&quot;,&quot;text&quot;:&quot;Why Use CASE WHEN with COUNT?&quot;,&quot;link&quot;:&quot;why-use-case-when-with-count&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Basic Syntax: CASE WHEN COUNT&quot;,&quot;text&quot;:&quot;Basic Syntax: CASE WHEN COUNT&quot;,&quot;link&quot;:&quot;basic-syntax-case-when-count&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Example: Analyzing Customer Orders&quot;,&quot;text&quot;:&quot;Real-World Example: Analyzing Customer Orders&quot;,&quot;link&quot;:&quot;real-world-example-analyzing-customer-orders&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Task: Count Orders by Value Range&quot;,&quot;text&quot;:&quot;Task: Count Orders by Value Range&quot;,&quot;link&quot;:&quot;task-count-orders-by-value-range&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Adding Time-Based Conditions&quot;,&quot;text&quot;:&quot;Adding Time-Based Conditions&quot;,&quot;link&quot;:&quot;adding-time-based-conditions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Usage: Multiple Conditions and Aggregations&quot;,&quot;text&quot;:&quot;Advanced Usage: Multiple Conditions and Aggregations&quot;,&quot;link&quot;:&quot;advanced-usage-multiple-conditions-and-aggregations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Task: Analyze High-Value Customers&quot;,&quot;text&quot;:&quot;Task: Analyze High-Value Customers&quot;,&quot;link&quot;:&quot;task-analyze-high-value-customers&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;CASE WHEN COUNT for Percentage Calculations&quot;,&quot;text&quot;:&quot;CASE WHEN COUNT for Percentage Calculations&quot;,&quot;link&quot;:&quot;case-when-count-for-percentage-calculations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Task: Calculate Product Category Distribution&quot;,&quot;text&quot;:&quot;Task: Calculate Product Category Distribution&quot;,&quot;link&quot;:&quot;task-calculate-product-category-distribution&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Combining CASE WHEN COUNT with Other Aggregations&quot;,&quot;text&quot;:&quot;Combining CASE WHEN COUNT with Other Aggregations&quot;,&quot;link&quot;:&quot;combining-case-when-count-with-other-aggregations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Task: Analyze Order Values and Counts by Customer Type&quot;,&quot;text&quot;:&quot;Task: Analyze Order Values and Counts by Customer Type&quot;,&quot;link&quot;:&quot;task-analyze-order-values-and-counts-by-customer-type&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Performance Considerations&quot;,&quot;text&quot;:&quot;Performance Considerations&quot;,&quot;link&quot;:&quot;performance-considerations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;text&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;link&quot;:&quot;common-pitfalls-and-how-to-avoid-them&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Wrapping Up&quot;,&quot;text&quot;:&quot;Wrapping Up&quot;,&quot;link&quot;:&quot;wrapping-up&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FAQ&quot;,&quot;text&quot;:&quot;FAQ&quot;,&quot;link&quot;:&quot;faq&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What does CASE WHEN COUNT mean in SQL?&quot;,&quot;text&quot;:&quot;What does CASE WHEN COUNT mean in SQL?&quot;,&quot;link&quot;:&quot;what-does-case-when-count-mean-in-sql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;How do I use CASE WHEN with COUNT()?&quot;,&quot;text&quot;:&quot;How do I use CASE WHEN with COUNT()?&quot;,&quot;link&quot;:&quot;how-do-i-use-case-when-with-count&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Can I use an update from select with multiple tables?&quot;,&quot;text&quot;:&quot;Can I use an update from select with multiple tables?&quot;,&quot;link&quot;:&quot;can-i-use-an-update-from-select-with-multiple-tables&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What is the difference between COUNT(*) and COUNT(CASE WHEN \\u2026)?&quot;,&quot;text&quot;:&quot;What is the difference between COUNT(*) and COUNT(CASE WHEN \\u2026)?&quot;,&quot;link&quot;:&quot;eb-table-content-19&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Why use CASE WHEN with COUNT() instead of separate queries?&quot;,&quot;text&quot;:&quot;Why use CASE WHEN with COUNT() instead of separate queries?&quot;,&quot;link&quot;:&quot;why-use-case-when-with-count-instead-of-separate-queries&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL CASE WHEN COUNT&quot;,&quot;value&quot;:&quot;breakdown-of-sql-case-when-count&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Why Use CASE WHEN with COUNT?&quot;,&quot;value&quot;:&quot;why-use-case-when-with-count&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Basic Syntax: CASE WHEN COUNT&quot;,&quot;value&quot;:&quot;basic-syntax-case-when-count&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Example: Analyzing Customer Orders&quot;,&quot;value&quot;:&quot;real-world-example-analyzing-customer-orders&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Task: Count Orders by Value Range&quot;,&quot;value&quot;:&quot;task-count-orders-by-value-range&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Adding Time-Based Conditions&quot;,&quot;value&quot;:&quot;adding-time-based-conditions&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Advanced Usage: Multiple Conditions and Aggregations&quot;,&quot;value&quot;:&quot;advanced-usage-multiple-conditions-and-aggregations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Task: Analyze High-Value Customers&quot;,&quot;value&quot;:&quot;task-analyze-high-value-customers&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;CASE WHEN COUNT for Percentage Calculations&quot;,&quot;value&quot;:&quot;case-when-count-for-percentage-calculations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Task: Calculate Product Category Distribution&quot;,&quot;value&quot;:&quot;task-calculate-product-category-distribution&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Combining CASE WHEN COUNT with Other Aggregations&quot;,&quot;value&quot;:&quot;combining-case-when-count-with-other-aggregations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Task: Analyze Order Values and Counts by Customer Type&quot;,&quot;value&quot;:&quot;task-analyze-order-values-and-counts-by-customer-type&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;value&quot;:&quot;common-pitfalls-and-how-to-avoid-them&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Wrapping Up&quot;,&quot;value&quot;:&quot;wrapping-up&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FAQ&quot;,&quot;value&quot;:&quot;faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What does CASE WHEN COUNT mean in SQL?&quot;,&quot;value&quot;:&quot;what-does-case-when-count-mean-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;How do I use CASE WHEN with COUNT()?&quot;,&quot;value&quot;:&quot;how-do-i-use-case-when-with-count&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Can I use an update from select with multiple tables?&quot;,&quot;value&quot;:&quot;can-i-use-an-update-from-select-with-multiple-tables&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What is the difference between COUNT(*) and COUNT(CASE WHEN \\u2026)?&quot;,&quot;value&quot;:&quot;what-is-the-difference-between-count-and-countcase-when-\\u2026&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Why use CASE WHEN with COUNT() instead of separate queries?&quot;,&quot;value&quot;:&quot;why-use-case-when-with-count-instead-of-separate-queries&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#breakdown-of-sql-case-when-count\">Breakdown of SQL CASE WHEN COUNT<\/a><li><a href=\"#why-use-case-when-with-count\">Why Use CASE WHEN with COUNT?<\/a><li><a href=\"#basic-syntax-case-when-count\">Basic Syntax: CASE WHEN COUNT<\/a><li><a href=\"#real-world-example-analyzing-customer-orders\">Real-World Example: Analyzing Customer Orders<\/a><li><a href=\"#advanced-usage-multiple-conditions-and-aggregations\">Advanced Usage: Multiple Conditions and Aggregations<\/a><li><a href=\"#case-when-count-for-percentage-calculations\">CASE WHEN COUNT for Percentage Calculations<\/a><li><a href=\"#combining-case-when-count-with-other-aggregations\">Combining CASE WHEN COUNT with Other Aggregations<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#common-pitfalls-and-how-to-avoid-them\">Common Pitfalls and How to Avoid Them<\/a><li><a href=\"#wrapping-up\">Wrapping Up<\/a><li><a href=\"#faq\">FAQ<\/a><ul class=\"eb-toc__list\"><li><a href=\"#what-does-case-when-count-mean-in-sql\">What does CASE WHEN COUNT mean in SQL?<\/a><li><a href=\"#how-do-i-use-case-when-with-count\">How do I use CASE WHEN with COUNT()?<\/a><li><a href=\"#can-i-use-an-update-from-select-with-multiple-tables\">Can I use an update from select with multiple tables?<\/a><li><a href=\"#eb-table-content-19\">What is the difference between COUNT(*) and COUNT(CASE WHEN \u2026)?<\/a><li><a href=\"#why-use-case-when-with-count-instead-of-separate-queries\">Why use CASE WHEN with COUNT() instead of separate queries?<\/a><\/li><\/ul><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Why Use CASE WHEN with COUNT?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>CASE WHEN COUNT<\/code> combination shines when you need to:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Count occurrences based on multiple conditions<\/li>\n\n\n\n<li>Create custom groupings on the fly<\/li>\n\n\n\n<li>Perform conditional aggregations<\/li>\n\n\n\n<li>Generate summary reports with various metrics<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s dive into some practical examples to see how this works in action.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Basic Syntax: CASE WHEN COUNT<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before we jump into complex scenarios, let&#8217;s review the basic syntax:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  COUNT(CASE WHEN condition THEN result END) AS count_name\nFROM\n  table_name;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> condition <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> result <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> count_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  table_name;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This structure allows you to count rows that meet specific conditions. The <code>CASE WHEN<\/code> statement acts as a filter, and <code>COUNT<\/code> tallies up the matches.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Example: Analyzing Customer Orders<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you&#8217;re working with an e-commerce database and want to analyze customer orders. You have a table called <code>orders<\/code> with columns like <code>order_id<\/code>, <code>customer_id<\/code>, <code>order_date<\/code>, and <code>total_amount<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Task: Count Orders by Value Range<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s say you want to count orders in different value ranges: under $50, $50-$100, and over $100.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  COUNT(CASE WHEN total_amount &lt; 50 THEN 1 END) AS low_value_orders,\n  COUNT(CASE WHEN total_amount BETWEEN 50 AND 100 THEN 1 END) AS mid_value_orders,\n  COUNT(CASE WHEN total_amount > 100 THEN 1 END) AS high_value_orders\nFROM\n  orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> total_amount <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> low_value_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> total_amount <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> mid_value_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> total_amount <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> high_value_orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query gives you a quick overview of order distribution across different value ranges. It&#8217;s much more efficient than running separate queries for each range.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Adding Time-Based Conditions<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s add a time dimension to our analysis. We&#8217;ll count orders for the last 30 days, 30-60 days ago, and 60-90 days ago.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  COUNT(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END) AS last_30_days,\n  COUNT(CASE WHEN order_date BETWEEN CURRENT_DATE - INTERVAL '60 days' AND CURRENT_DATE - INTERVAL '30 days' THEN 1 END) AS days_30_to_60,\n  COUNT(CASE WHEN order_date BETWEEN CURRENT_DATE - INTERVAL '90 days' AND CURRENT_DATE - INTERVAL '60 days' THEN 1 END) AS days_60_to_90\nFROM\n  orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> order_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;30 days&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> last_30_days,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> order_date <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;60 days&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;30 days&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> days_30_to_60,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> order_date <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;90 days&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;60 days&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> days_60_to_90<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides insights into recent order trends without the need for multiple date-range filters.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Usage: Multiple Conditions and Aggregations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The real power of <code>CASE WHEN COUNT<\/code> comes into play when you combine multiple conditions and aggregations.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Task: Analyze High-Value Customers<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s identify and count high-value customers based on their order history.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  COUNT(CASE WHEN total_orders > 10 AND total_spend > 1000 THEN 1 END) AS high_value_customers,\n  COUNT(CASE WHEN total_orders > 5 AND total_spend > 500 THEN 1 END) AS potential_high_value\nFROM (\n  SELECT\n    customer_id,\n    COUNT(*) AS total_orders,\n    SUM(total_amount) AS total_spend\n  FROM\n    orders\n  GROUP BY\n    customer_id\n) customer_summary;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> total_orders <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> total_spend <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> high_value_customers,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> total_orders <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> total_spend <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> potential_high_value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    customer_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> total_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(total_amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> total_spend<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) customer_summary;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses a subquery to calculate total orders and spend per customer, then applies <code>CASE WHEN COUNT<\/code> to categorize and count customers based on these metrics.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">CASE WHEN COUNT for Percentage Calculations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Another practical use of <code>CASE WHEN COUNT<\/code> is calculating percentages within your dataset.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Task: Calculate Product Category Distribution<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose you have a <code>products<\/code> table with a <code>category<\/code> column. You want to see the distribution of products across categories as percentages.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  category,\n  COUNT(*) AS category_count,\n  COUNT(*) * 100.0 \/ SUM(COUNT(*)) OVER () AS category_percentage\nFROM\n  products\nGROUP BY\n  category;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> category_count,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) * <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> \/ <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*)) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> () <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> category_percentage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  products<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  category;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses <code>COUNT(*)<\/code> with <code>CASE WHEN<\/code> implicitly (the <code>CASE WHEN<\/code> is applied by the <code>GROUP BY<\/code>). The <code>OVER ()<\/code> clause creates a window function that calculates the total count across all categories.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Combining CASE WHEN COUNT with Other Aggregations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You can mix <code>CASE WHEN COUNT<\/code> with other aggregation functions for more comprehensive analysis.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Task: Analyze Order Values and Counts by Customer Type<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s say you have a <code>customers<\/code> table with a <code>customer_type<\/code> column (e.g., &#8216;retail&#8217;, &#8216;wholesale&#8217;, &#8216;VIP&#8217;). You want to analyze both the count and total value of orders for each customer type.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT\n  c.customer_type,\n  COUNT(CASE WHEN o.total_amount &lt; 100 THEN 1 END) AS low_value_orders,\n  COUNT(CASE WHEN o.total_amount >= 100 AND o.total_amount &lt; 500 THEN 1 END) AS mid_value_orders,\n  COUNT(CASE WHEN o.total_amount >= 500 THEN 1 END) AS high_value_orders,\n  SUM(CASE WHEN o.total_amount &lt; 100 THEN o.total_amount ELSE 0 END) AS low_value_total,\n  SUM(CASE WHEN o.total_amount >= 100 AND o.total_amount &lt; 500 THEN o.total_amount ELSE 0 END) AS mid_value_total,\n  SUM(CASE WHEN o.total_amount >= 500 THEN o.total_amount ELSE 0 END) AS high_value_total\nFROM\n  customers c\nJOIN\n  orders o ON c.customer_id = o.customer_id\nGROUP BY\n  c.customer_type;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_type<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> low_value_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> mid_value_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> high_value_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> low_value_total,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> mid_value_total,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> high_value_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  orders o <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_type<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a detailed breakdown of order counts and values across different customer types and order value ranges.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While <code>CASE WHEN COUNT<\/code> is incredibly useful, it&#8217;s important to consider performance, especially when working with large datasets.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Indexing: Ensure that columns used in <code>CASE WHEN<\/code> conditions are properly indexed.<\/li>\n\n\n\n<li>Subqueries: For complex conditions, using subqueries (as in the high-value customers example) can sometimes be more efficient than multiple <code>CASE WHEN<\/code> statements.<\/li>\n\n\n\n<li>Materialized Views: For frequently run analyses, consider creating materialized views that pre-compute these conditional counts.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Common Pitfalls and How to Avoid Them<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Null Handling: Remember that <code>COUNT(expression)<\/code> only counts non-null values. If your <code>CASE WHEN<\/code> might return null, use <code>COUNT(*)<\/code> instead:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>   COUNT(CASE WHEN condition THEN 1 ELSE NULL END)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> condition <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">vs.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>   COUNT(*) FILTER (WHERE condition)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">FILTER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> condition)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Exclusive Conditions: Ensure your <code>CASE WHEN<\/code> conditions are mutually exclusive if you&#8217;re categorizing data. Overlapping conditions can lead to double-counting.<\/li>\n\n\n\n<li>Date and Time Zones: When working with date\/time conditions, be aware of time zone differences. Use consistent time zone handling in your queries.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Wrapping Up<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>CASE WHEN COUNT<\/code> technique in SQL is a versatile tool for data analysis. It allows you to create nuanced, condition-based counts in a single query, reducing the need for multiple queries or complex joins. By using this approach, you&#8217;ll be able to write more efficient, readable SQL code and derive insights from your data more quickly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, the key to effective use of <code>CASE WHEN COUNT<\/code> is to think about your data categorization needs upfront. What conditions are important for your analysis? How can you group these conditions efficiently? With practice, you&#8217;ll find yourself reaching for this technique regularly in your SQL toolkit.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<ol class=\"rank-math-list \">\n<li id=\"faq-question-1771985858796\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>What does <code>CASE WHEN COUNT<\/code> mean in SQL?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p><code>CASE WHEN COUNT<\/code> combines the <code>CASE<\/code> expression and the <code>COUNT()<\/code> function to conditionally count rows based on criteria within a query. It lets you calculate counts for specific conditions in the same result set.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985909936\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>How do I use <code>CASE WHEN<\/code> with <code>COUNT()<\/code>?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>You include the <code>CASE WHEN<\/code> expression inside the <code>COUNT()<\/code> function to count records that meet certain conditions. For example:<\/p>\n<p>COUNT(CASE WHEN status = &#8216;active&#8217; THEN 1 END)<\/p>\n<p>This counts only the rows where the <code>status<\/code> is <code>'active'<\/code>.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985985770\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>Can I use an update from select with multiple tables?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Yes, you can join multiple tables in your <code><code>UPDATE FROM SELECT<\/code><\/code> query to retrieve and update data based on complex conditions.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985994514\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>What is the difference between <code>COUNT(*)<\/code> and <code>COUNT(CASE WHEN \u2026)<\/code>?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Yes. By using multiple <code>COUNT(CASE WHEN \u2026)<\/code> expressions in the SELECT list, you can compute different counts, such as active vs inactive users, in one aggregated query.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986003404\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>Why use <code>CASE WHEN<\/code> with <code>COUNT()<\/code> instead of separate queries?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Using <code>CASE WHEN<\/code> with <code>COUNT()<\/code> in a single query <strong>improves performance and readability<\/strong>, since you avoid multiple separate queries and get all conditional counts in one result set.<\/p>\n\n<\/div>\n<\/li>\n<\/ol>\n<\/div><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL CASE WHEN COUNT SQL&#8217;s CASE WHEN statement combined with the COUNT function is a great for data analysis. This combo lets you create custom categories and count occurrences within those categories in a single query. If you&#8217;ve ever found yourself running multiple queries to get counts for different conditions, this technique will [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4209,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4208","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-CASE-WHEN-COUNT-Practical-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL CASE WHEN COUNT SQL&#8217;s CASE WHEN statement combined with the COUNT function is a great for data analysis. This combo lets you create custom categories and count occurrences within those categories in a single query. If you&#8217;ve ever found yourself running multiple queries to get counts for different conditions, this technique will&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4208"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4208\/revisions"}],"predecessor-version":[{"id":17819,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4208\/revisions\/17819"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4209"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}