SQL Group By status

Question

I am looking for a way to group data by status. I have this with a simple select, just for demonstration:

MS SQL Server 2014 Schema Setup:

CREATE TABLE Projects ([ID] int, [Project] varchar(10));
INSERT INTO Projects ([ID], [Project])
VALUES
    (1, 'Admin'),
    (2, 'Accounts'),
    (3, 'Finance')
;

CREATE TABLE Tasks ([ID] int, [ProjectID] int, [Task] int, [Status] varchar(8));
INSERT INTO Tasks
    ([ID], [ProjectID], [Task], [Status])
VALUES
    (1, 1, '001', 'Open'),
    (2, 1, '002', 'Canceled'),
    (3, 1, '003', 'Canceled'),
    (4, 1, '004', 'Canceled'),
    (5, 2, '005', 'Rejected'),
    (6, 2, '006', 'Canceled'),
    (7, 2, '007', 'Canceled'),
    (8, 3, '008', 'Canceled'),
    (9, 3, '009', 'Canceled'),
    (10, 3, '010', 'Canceled'),
    (11, 3, '011', 'Canceled'),
    (12, 3, '0012', 'Rejected')
;

Query 1:

SELECT
  Projects.Project,
  COUNT(Tasks.Status) AS Total
FROM
  Projects
  LEFT JOIN Tasks ON Projects.ID = Tasks.ProjectID
 GROUP BY
   Projects.Project

Results:

|  Project | Total |
|----------|-------|
| Accounts |     3 |
|    Admin |     4 |
|  Finance |     5 |

But I am looking for a way to output this:

|  Project | Open | Canceled | Rejected |
|----------|------|----------|----------|
|    Admin |    1 |        3 |          |
| Accounts |      |        2 |        1 |
|  Finance |      |        4 |        1 |

The desired result groups the Project by Tasks Status. How can I do this? Thanks.

Check the answers in this question, https://stackoverflow.com/questions/1241178/mysql-rows-to-columns, it should help you deal with your own problem — Floaterz, Sep 19 '17 at 13:14

score 3 · Accepted Answer · answered Sep 19 '17 at 13:13

You can use conditional aggregation:

SELECT
    Projects.Project
  , [Open]   = count(case when tasks.status='open' then 1 end)
  , Canceled = count(case when tasks.status='Canceled ' then 1 end)
  , Rejected = count(case when tasks.status='Rejected ' then 1 end)
  , Total = COUNT(Tasks.Status)
FROM
  Projects
  LEFT JOIN Tasks ON Projects.ID = Tasks.ProjectID
 GROUP BY
   Projects.Project

Fiddle: http://sqlfiddle.com/#!6/4b862/4/0

returns:

+----------+------+----------+----------+-------+
| Project  | Open | Canceled | Rejected | Total |
+----------+------+----------+----------+-------+
| Accounts |    0 |        2 |        1 |     3 |
| Admin    |    1 |        3 |        0 |     4 |
| Finance  |    0 |        4 |        1 |     5 |
+----------+------+----------+----------+-------+

score 3 · Answer 2 · answered Sep 19 '17 at 13:18

You can use this query.

SELECT * FROM 
(
    SELECT
      Projects.Project,
      Tasks.Status
    FROM
      Projects
      LEFT JOIN Tasks ON Projects.ID = Tasks.ProjectID
) Src 
    PIVOT (COUNT(Status) FOR Status IN ([Open] ,[Canceled],[Rejected])) P

Result:

Project    Open        Canceled    Rejected
---------- ----------- ----------- -----------
Accounts   0           2           1
Admin      1           3           0
Finance    0           4           1

SQL Group By status

2 Answers2