Grouping issue

Question

Hello,

I have table below and need a summary of the table.

here is the source data; and i need Output below;

could anybody please help? Much appreciated.

Answer 1

Try the below one:

SELECT	DISTINCT Name,
		Temp.Completed,
		(Temp.Completed * 100.0) / Temp.Total AS CompletedPercent,
		Temp.Pending,
		Temp.Total
FROM		Table1 TOuter
CROSS APPLY	(
				SELECT	COUNT(CASE WHEN Status = 'PENDING' THEN 1 ELSE NULL END) AS Pending
				,		COUNT(CASE WHEN Status = 'COMPLETED' THEN 1 ELSE NULL END) AS Completed
				,		COUNT(Status) AS Total
				FROM	Table1 TInner
				WHERE	TOuter.Name = TInner.Name
			) Temp

[OR]

WITH CTE AS
(
	SELECT	Name,
			COUNT(CASE WHEN Status = 'PENDING' THEN 1 ELSE NULL END) AS Pending,
			COUNT(CASE WHEN Status = 'COMPLETED' THEN 1 ELSE NULL END) AS Completed,
			COUNT(Status) AS Total
	FROM	Table1	-- change with your table name
	GROUP BY Name
)
SELECT	Name,
		Completed,
		(Completed * 100.0)/ Total AS CompletedPercent
		Pending,
		Total
FROM	CTE

Thanks!

Answer 2

Please go through these given links.

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

these links shows the PIVOT and UNPIVOTING concepts with example.

Answer 3

Try

select name, (count(id)-count(case status='PENDING' THEN ID else null end))/count(id) * 100 as Completed, count(case status='PENDING' THEN ID else null end) as Pending, count(id) as Total
from tab1
group by name;
go

---

Many Thanks & Best Regards, Hua Min

Answer 4

Hi Vick,

Here is a coded solution for this ;

DECLARE @test table ( 
name VARCHAR(10),
ID INT,
status VARCHAR(25)
);

INSERT INTO @test
SELECT 'ABC',123457,'PENDING' UNION ALL
SELECT 'ABC',123467,'PENDING' UNION ALL
SELECT 'ABC',123477,'COMPLETED' UNION ALL
SELECT 'ABC',123487,'PENDING' UNION ALL
SELECT 'XYZ',124457,'PENDING' UNION ALL
SELECT 'XYZ',125467,'PENDING' UNION ALL
SELECT 'XYZ',126477,'PENDING' UNION ALL
SELECT 'XYZ',127487,'COMPLETED';


SELECT
 name,
  (total-pending)*100/total as '% completed',
  pending,
  total
  FROM (
SELECT
	name,
	SUM(case status when 'PENDING' then 1
						else 0 
						end) as 'pending',
	COUNT(status) as total
from @test
group by name) as a;

-- Results

name       % completed pending     total
---------- ----------- ----------- -----------
ABC        25          3           4
XYZ        25          3           4

(2 row(s) affected)

It would be really appreciated if your question has the DDL of tables and the scripts for populating data, rather than screenshots of the same, so that its easier to code and test a solution. 

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 5

Try This...

SELECT
    NAME,
    (COMPLETED*1.00)/(TOTAL*1.00)*100.00 AS COMPLETED,
    PENDING,
    TOTAL
FROM
(
SELECT
    NAME,
    SUM(CASE WHEN STATUS = 'PENDING' THEN 1 ELSE 0 END) AS PENDING,
    SUM(CASE WHEN STATUS = 'COMPLETED' THEN 1 ELSE 0 END) AS COMPLETED,
    SUM(1) AS TOTAL
FROM Test
GROUP BY NAME )T

------------------------------------------------------------------------------------

Please mark as answer or vote if it helps you.

Answer 6

Below SQL will do this:

DECLARE @tbl TABLE
(
	Id			INT			NOT NULL IDENTITY(1,1)
	,Name		VARCHAR(8)
	,[Status]	VARCHAR(16)
)

INSERT INTO @tbl(Name, [Status])
	SELECT 'A', 'Pending'	UNION ALL
	SELECT 'A', 'Pending'	UNION ALL
	SELECT 'A', 'Completed' UNION ALL
	SELECT 'A', 'Pending'	UNION ALL
	SELECT 'B', 'Pending'	UNION ALL
	SELECT 'B', 'Completed' UNION ALL
	SELECT 'B', 'Pending'	UNION ALL
	SELECT 'B', 'Pending'	UNION ALL
	SELECT 'B', 'Pending'	UNION ALL
	SELECT 'C', 'Pending'	UNION ALL
	SELECT 'C', 'Pending'	UNION ALL
	SELECT 'C', 'Pending'	UNION ALL
	SELECT 'C', 'Completed' 
	
SELECT * FROM @tbl	

SELECT
	Y.Name
	,(CONVERT(VARCHAR(16), ((Y.Total-COUNT([Status]))/(CONVERT(FLOAT,Y.Total)))*100) + '%') AS [Completed]
	,COUNT([Status]) AS [Pending]
	,Y.Total
FROM
	@tbl X LEFT JOIN 
	(
		SELECT
			Name
			,COUNT([Status]) AS [Total]
		FROM
			@tbl
		GROUP BY
			Name
	) Y 
	ON X.Name = Y.Name AND X.[Status] = 'Pending'
WHERE
	Y.Name IS NOT NULL	
GROUP BY
	Y.Name 
	,Y.Total 
ORDER BY
	Y.Name
	
	

---

Thanks & Regards,
Please do "Mark As Answer" if this helps you.