Question 236661 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to include 0 as count in sql?

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+

account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

  • "Low Salary": All the salaries strictly less than $20000. "Average
  • Salary": All the salaries in the inclusive range [$20000, $50000].
  • "High Salary": All the salaries strictly greater than $50000.

The result table must contain all three categories. If there are no
accounts in a category, return 0.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Accounts table:

+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+

Output:

+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+

Explanation:
Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.

link to the problem statement
I tried this

SELECT 
CASE 
WHEN income < 20000 THEN "Low Salary"
WHEN income >= 20000 AND income <= 50000 THEN "Average Salary"
ELSE "High Salary"
END AS category,
COUNT(*) AS accounts_count
FROM Accounts 
GROUP BY category;

My output is this

| category    | accounts_count |
| ----------- | -------------- |
| High Salary | 3              |
| Low Salary  | 1              |

But expected output is this

| category       | accounts_count |
| -------------- | -------------- |
| High Salary    | 3              |
| Low Salary     | 1              |
| Average Salary | 0              |
Tags:

2

Answers


  1. 0

    You can use union all to generate the desired output. The first query is used to find accounts with low salaries, the second query is used to find accounts with average salaries, and the last query is used to find accounts with high salaries. We combine these three queries using union all:

    select 'Low Salary' as category,
       count(*) as accounts_count
from accounts
where income < 20000
union all
select 'Average Salary',
       count(*)
from accounts
where income between 20000 and 50000
union all
select 'High Salary',
       count(*)
from accounts
where income > 50000;

    Demo: https://dbfiddle.uk/eu1EIDwW

    Login or Signup to reply.
  2. 0

    Try this:

    select 'Low Salary' as category, sum(case when income<20000 then 1 else 0 end)  as accounts_count
from accounts
union
select 'Average Salary' ,sum(case when income >= 20000 AND income <= 50000 then 1 else 0 end) 
from accounts
union
select 'High Salary', sum(case when income > 50000 then 1 else 0 end) 
from accounts
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search