CODEWITHSUNDEEP
sql
Irshad Saiyed
Irshad Saiyed

Reputation: 11

SELECT DISTINCT HAVING Count unique conditions

I've searched for an answer on this but can't find quite how to get this distinct recordset based on a condition. I have a table with the following sample data:

+---------+-------------+
| Branch  | Task Status |
+---------+-------------+
| Account | Completed   |
| HR      | Completed   |
| Account | Completed   |
| HR      | Not Define  |
| Account | Uncompleted |
| Account | Not Define  |
| Account | Completed   |
| HR      | Uncompleted |
| HR      | Uncompleted |
| HR      | Completed   |
| HR      | Not Define  |
+---------+-------------+

I'd like to create a query that shows the count of total task and also want total of Task Status wise numbers of tasks, e.g.

Branch  Total Task  Completed   Uncompleted Not Define
Account       5            3        1         1
Admin         6            2        2         2

Upvotes: 1

Views: 981

Answers (5)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

In SQL Server2005+ you can use PIVOT operator

SELECT *
FROM (
      SELECT *, COUNT(*) OVER(PARTITION BY Branch) AS TotalTask
      FROM dbo.task
      ) p    
PIVOT
 (
  COUNT(p.Task) FOR p.Task IN ([Completed], [Uncompleted], [Not Define])
  ) x

Demo on SQLFiddle

Upvotes: 0

Harshil
Harshil

Reputation: 411

Try this

SELECT * FROM (SELECT 
ROW_NUMBER() OVER(PARTITION BY Branch ORDER BY [Task Status]) AS No,
Branch,
sum (CASE WHEN [Task Status] = 'Completed' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Completed],
sum (CASE WHEN [Task Status] = 'Not Define' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Not Define],
sum (CASE WHEN [Task Status] = 'Uncompleted' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Uncompleted]
 FROM task ) AS T1 WHERE No = 1

or

SELECT * FROM (SELECT 
ROW_NUMBER() OVER(PARTITION BY Branch ORDER BY [Task Status]) AS No,
Branch,
count (CASE WHEN [Task Status] = 'Completed' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Completed],
count (CASE WHEN [Task Status] = 'Not Define' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Not Define],
count (CASE WHEN [Task Status] = 'Uncompleted' THEN 1 ELSE 0 END ) OVER (PARTITION BY Branch) AS [Uncompleted]
 FROM task ) AS T1 WHERE No = 1

Upvotes: 0

nvogel
nvogel

Reputation: 25534

SELECT branch,
COUNT(*),
COUNT(CASE taskstatus WHEN 'Completed' THEN 1 END),
COUNT(CASE taskstatus WHEN 'Uncompleted' THEN 1 END),
COUNT(CASE taskstatus WHEN 'Not Define' THEN 1 END)
FROM YourTable
GROUP BY branch;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425763

SELECT  branch,
        COUNT(*),
        SUM(CASE status WHEN 'completed' THEN 1 END) AS completed,
        SUM(CASE status WHEN 'uncompleted' THEN 1 END) AS uncompleted,
        SUM(CASE status WHEN 'not define' THEN 1 END) AS not_define
FROM    task
GROUP BY
        branch

Upvotes: 2

Cyril Gandon
Cyril Gandon

Reputation: 17068

SELECT Branch
     , COUNT(1) as Total
     , COUNT(CASE WHEN TaskStatus = 'Completed' THEN 1 ELSE 0 END) AS Completed
     , COUNT(CASE WHEN TaskStatus = 'Uncompleted' THEN 1 ELSE 0 END) AS Uncompleted
     , COUNT(CASE WHEN TaskStatus = 'Not Define' THEN 1 ELSE 0 END) AS NotDefine
FROM Table
GROUP BY Branch

Upvotes: 1

Related Questions