CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
IanCian
IanCian

Reputation: 1106

SQL Query to get count of one status + all other status as one

I have a table which holds a set of records. Now through SQL I am getting a Count for each different status. Example from the table below:

    Status      | ID
-------------------------
    Open        | 1
    Open        | 2
    Open        | 3
    Open        | 4
    In Progress | 5
    In Progress | 6
    In Progress | 7
    Closed      | 8
    Closed      | 9
    Closed      | 10
    Closed      | 11

So Far I managed to do the following

Status      | ID
-------------------------
Open        | 4
In Progress | 3
Closed      | 4

However I want the result to return

   Status      | ID
-------------------------
   Other       | 7
   Closed      | 4

The following is my SQL so far:

SELECT myStatus,Count(job) As CountJobs
FROM JobsTable
GROUP BY myStatus
ORDER BY CountJobs DESC

How can I achieve this?

Upvotes: 0

Views: 3274

Answers (4)

Tanner
Tanner

Reputation: 22733

You could modify the query to use a CASE statement to replace anything other than Closed with Other in a subquery and GROUP the results of that:

SELECT  [Status] ,
        COUNT([Status]) StatusCount
FROM    ( SELECT    CASE WHEN [Status] != 'Closed' THEN 'Other'
                         ELSE [Status]
                    END AS [Status]
          FROM      JobsTable
        ) t
GROUP BY [Status]
ORDER BY StatusCount DESC

Full testable query:

CREATE TABLE #JobsTable
    ([Status] varchar(11), [ID] int)
;

INSERT INTO #JobsTable
    ([Status], [ID])
VALUES
    ('Open', 1),
    ('Open', 2),
    ('Open', 3),
    ('Open', 4),
    ('In Progress', 5),
    ('In Progress', 6),
    ('In Progress', 7),
    ('Closed', 8),
    ('Closed', 9),
    ('Closed', 10),
    ('Closed', 11)
;

SELECT  [Status] ,
        COUNT([Status]) StatusCount
FROM    ( SELECT    CASE WHEN [Status] != 'Closed' THEN 'Other'
                         ELSE [Status]
                    END AS [Status]
          FROM      #JobsTable
        ) t
GROUP BY [Status]
ORDER BY StatusCount DESC   

DROP TABLE #JobsTable

Produces:

Status  StatusCount
Other   7
Closed  4

Demo SQL Fiddle

Upvotes: 1

Grażynka
Grażynka

Reputation: 29

Another solution: use WITH clause as below

WITH src AS (
  SELECT CASE WHEN status = 'Closed' THEN 'Closed'
         ELSE 'Other'
         END as Status,
         Id
    from JobsTable)
SELECT src.Status, count(src.Id)
  FROM src
 GROUP BY src.Status;

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT CASE 
           WHEN Status = 'Closed' 
           THEN Status 
       ELSE 'OTHER' 
       END  AS Status,
       Count(job) As CountJobs
FROM   JobsTable
GROUP BY CASE 
           WHEN Status = 'Closed' 
           THEN Status 
       ELSE 'OTHER' 
       END
ORDER BY CountJobs DESC

Upvotes: 2

Lamak
Lamak

Reputation: 70638

Use a CASE expression:

SELECT  CASE WHEN myStatus = 'Closed' THEN 'Closed' ELSE 'Other' END myStatus,
        COUNT(*) CountJobs
FROM JobsTable
GROUP BY CASE WHEN myStatus = 'Closed' THEN 'Closed' ELSE 'Other' END;

Upvotes: 3

Related Questions