SQL query - Beginner

Question

I'm new to SQL. I need SQL query to achieve the mentioned output. I have asked a similar query but that doesn't describe my problem well. Here is my detailed requirement.

I have a table with data as below

Table: boxes
+------------+----------+
| box_id     | Status   | 
+------------+----------+
| 1          | created  |
| 2          | created  |
| 3          | opened   |
| 4          | opened   |
| 5          | closed   |
| 6          | closed   |
| 7          | closed   |
| 8          | wrapped  |
+------------+----------+

With this there is also a status names destroyed But for which there is no box destroyed.

I need an output like this

+--------------+-------+
| Status       | Count |
+--------------+-------+
| created      | 2     |
| opened       | 2     |
| destroyed    | 0     |
| other_status | 4     |    # this includes status (closed and wrapped)
| total        | 8     |
+--------------+-------+

How can this be achieved in SQL. Thanks in advance

What code have you written already? Please have a read of this guide on producing code for a good quality question, then include and mark up your code in your question. Cheers! — Joundill
– Joundill, Commented Jun 17, 2020 at 19:00

mkRabbani · Accepted Answer · 2020-06-17 15:03:58Z

1

If you are using MSSQL or MySQL8.0, you can use CTE as below to achieve your required output-

DEMO HERE

WITH CTE AS 
(
    SELECT 'created' Status UNION ALL
    SELECT 'opened'  UNION ALL
    SELECT 'destroyed'  UNION ALL
    SELECT 'other_status' 
)
,CTE2 AS
(
    SELECT
    CASE 
        WHEN Status IN ('created','opened','destroyed') THEN Status 
        ELSE 'other_status' 
    END Status,
    SUM(1) Cnt
    FROM your_table
    GROUP BY
    CASE 
        WHEN Status IN ('created','opened','destroyed') THEN Status 
        ELSE 'other_status' 
    END
)


SELECT CTE.Status,ISNULL(CTE2.Cnt, 0) Cnt
FROM CTE LEFT JOIN CTE2 ON CTE.Status = CTE2.Status

UNION ALL

SELECT 'Total' Status, SUM(CTE2.Cnt) FROM CTE2

answered Jun 17, 2020 at 15:03

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Subi Over a year ago

Thanks for the suggestion But I want to achieve this in SQL

mkRabbani Over a year ago

SQL is not the database for sure :) which database you are using?

mkRabbani Over a year ago

BTW, you can use the given logic for any database @Subi

Subi Over a year ago

mysql it is. Either MySql or Amazon Redshift - I use both

mkRabbani Over a year ago

MySQL version ?

|

monjo44 · Accepted Answer · 2020-06-17 15:42:14Z

1

you can try the following code.

select status, count(box_id) 
  from table 
 where status in ('created','opened', 'destroyed')  
 group by status

UNION ALL

select 'other_status' status, count(box_id) 
  from table 
 where status not in ('created','opened', 'destroyed')  

UNION ALL

select 'total' status, count(box_id) 
  from table;

edited Jun 17, 2020 at 15:42

answered Jun 17, 2020 at 15:33

monjo44

419 bronze badges

3 Comments

Subi Over a year ago

Is this efficient query? In my DB the Table contains 100 million rows. This way I need to run same query thrice

monjo44 Over a year ago

You can just try it. Its the best way to find out if it is efficient. In my opinion it should be efficient.

Subi Over a year ago

Thanks for the answer. But it is less efficient with the volume of data I am managing

Collectives™ on Stack Overflow

SQL query - Beginner

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related