How to get Column data by sub grouping

Question

I m trying to get the Status percentage by different status wise and we have 4 types of status. Here is the data for Columns below.

MR_ID Sup_ID    Status

1   1   Rejected    
1   1   Accepted    
1   1   Accepted    
1   1   Rejected    
2   2   InProgress  
2   2   InProgress  
2   2   Accepted    
2   2   Fordwarded

Like for MR_ID:1 and Sup_ID:1 combination the % of "Accepted" is 50% and % of "Rejected" is 50%

Need to write a sql query or Stored Procedure to find it out in sql server

Not getting any clue.

Specify the expected result as well. And show us your current query attempt. — jarlh
– jarlh, Commented Jun 17, 2019 at 9:35
They're clearly using T-SQL in Oracle SQLDeveloper @jarhl. :/ — Thom A
– Thom A ♦, Commented Jun 17, 2019 at 9:46

Pratik Bhavsar · Accepted Answer · 2019-06-17 09:49:41Z

1

You are looking for a simple group by statement:

SELECT 
    MR_ID,
    Sup_ID,
    (CAST(SUM(CASE WHEN [Status] ='InProgress' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'InProgress',
    (CAST(SUM(CASE WHEN [Status] ='Fordwarded' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Fordwarded',
    (CAST(SUM(CASE WHEN [Status] ='Accepted' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Accepted',
    (CAST(SUM(CASE WHEN [Status] ='Rejected' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Rejected'
FROM 
    @Table1 t
GROUP BY 
    MR_ID,
    Sup_ID

Result:

answered Jun 17, 2019 at 9:49

Pratik Bhavsar

8481 gold badge10 silver badges35 bronze badges

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

Comments

MJoy · Accepted Answer · 2019-06-17 09:49:01Z

The following query should do what you want, the solution is for SQL server:

CREATE TABLE #temp (MR_ID INT, Sup_ID INT, [Status] VARCHAR(20))

INSERT INTO #temp VALUES
(1,1,'Rejected'),
(1,1,'Accepted'), 
(1,1,'Accepted'), 
(1,1,'Rejected'), 
(2,2,'InProgress'), 
(2,2,'InProgress'),
(2,2,'Accepted'),
(2,2,'Fordwarded') 

SELECT t.MR_ID, t.Sup_ID, t.[Status], (COUNT(t.[Status]) * 100 / (SELECT COUNT(*) FROM #temp WHERE MR_ID = t.MR_ID AND Sup_ID = t.Sup_ID )) AS [Percentage]
FROM #temp t
GROUP BY MR_ID, Sup_ID, [Status]

The result is as below,

MR_ID   Sup_ID  Status      Percentage
1       1       Accepted    50
1       1       Rejected    50
2       2       Accepted    25
2       2       Fordwarded  25
2       2       InProgress  50

AndreaM · Accepted Answer · 2019-06-17 10:11:34Z

You could try this:

CREATE TABLE TMP_PERC(
FIELD1 INT NOT NULL
, FIELD2 INT NOT NULL
, DESCR VARCHAR (100) NOT NULL
)


INSERT INTO TMP_PERC VALUES(1, 1, 'Rejected');
INSERT INTO TMP_PERC VALUES(1, 1, 'Accepted');
INSERT INTO TMP_PERC VALUES(1, 1, 'Accepted');
INSERT INTO TMP_PERC VALUES(1, 1, 'Rejected');
INSERT INTO TMP_PERC VALUES(2, 2, 'InProgress');
INSERT INTO TMP_PERC VALUES(2, 2, 'InProgress');
INSERT INTO TMP_PERC VALUES(2, 2, 'Accepted');
INSERT INTO TMP_PERC VALUES(2, 2, 'Fordwarded');

SELECT FD.FIELD1
     , FD.FIELD2
     , FD.DESCR
     , CAST(FD.TOTAL_DESCR AS FLOAT) / CAST(FC.TOTAL  AS FLOAT) * 100 AS PERC
FROM
(
SELECT FIELD1
     , FIELD2
     , DESCR
     , COUNT(*) AS TOTAL_DESCR   
FROM TMP_PERC TP_DECSCR
GROUP BY FIELD1
       , FIELD2
       , DESCR
) FD
INNER JOIN
(

SELECT FIELD1
     , FIELD2
     , COUNT(*) AS TOTAL
FROM TMP_PERC
GROUP BY FIELD1
       , FIELD2
) FC             
ON FD.FIELD1 = FC.FIELD1
AND FD.FIELD2 = FC.FIELD2
;

Gordon Linoff · Accepted Answer · 2019-06-17 10:49:54Z

0

I would simply do:

select mr_id, sup_id,
       avg(case when status = 'Accepted' then 1.0 else 0 end) as ratio_accepted,
       avg(case when status = 'Rejected' then 1.0 else 0 end) as ratio_rejected,
       avg(case when status = 'InProgress' then 1.0 else 0 end) as ratio_inprogress,
       avg(case when status = 'Forwarded' then 1.0 else 0 end) as ratio_forwarded
from t
group by mr_id, sup_id;

If you want the values in separate rows rather than columns, use window functions:

select mr_id, sup_id, status,
       count(*) * 1.0 / sum(count(*)) over (partition by mr_id, sub_id) as ratio
from t
group by mr_id, sup_id, status;

These queries are standard SQL and should work in both SQL Server and Oracle.

answered Jun 17, 2019 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

MJoy · Accepted Answer · 2019-06-17 10:56:14Z

0

msql way, don`t kown did it help

SELECT
    MR_ID mrId,
    `Status` `Status`,
    count(DISTINCT `Status`) / count(*) * 100 per
FROM
    xxx
GROUP BY
    mrId, `Status`[enter image description here][1]

edited Jun 17, 2019 at 10:56

MJoy

1,3692 gold badges10 silver badges24 bronze badges

answered Jun 17, 2019 at 10:07

jonas

213 bronze badges

Collectives™ on Stack Overflow

How to get Column data by sub grouping

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related