CODEWITHSUNDEEP
sqlsql-server
user1916528
user1916528

Reputation: 389

How to Group Results Onto One Row

Using the query below, my results are:

+----------+-------+-------+-------+-------+-------+  
| Adjuster |pending| assnd | qardy | inqa  | clsd  |  
+----------+-------+-------+-------+-------+-------+  
|   Jim    |   0   |   0   |   0   |   0   |   2   |  
+----------+-------+-------+-------+-------+-------+  
|   Jim    |   0   |   0   |   3   |   0   |   0   |      
+----------+-------+-------+-------+-------+-------+  
|   Bob    |   0   |   5   |   0   |   0   |   0   |    
+----------+-------+-------+-------+-------+-------+   
|   Bob    |   0   |   0   |   0   |   0   |   1   |    
+----------+-------+-------+-------+-------+-------+   
|   Mary   |   0   |   1   |   0   |   0   |   0   |    
+----------+-------+-------+-------+-------+-------+   
|   Mary   |   0   |   0   |   0   |   0   |   3   |    
+----------+-------+-------+-------+-------+-------+ 
|   Stacy  |   0   |   12  |   0   |   0   |   0   |    
+----------+-------+-------+-------+-------+-------+

I would like each Adjuster to be listed only once, and have all of their results on the same row as in the example below:

+----------+-------+-------+-------+-------+-------+  
| Adjuster |pending| assnd | qardy | inqa  | clsd  |  
+----------+-------+-------+-------+-------+-------+  
|   Jim    |   0   |   0   |   3   |   0   |   2   |  
+----------+-------+-------+-------+-------+-------+   
|   Bob    |   0   |   5   |   0   |   0   |   1   |    
+----------+-------+-------+-------+-------+-------+      
|   Mary   |   0   |   1   |   0   |   0   |   3   |    
+----------+-------+-------+-------+-------+-------+    
|   Stacy  |   0   |   12  |   0   |   0   |   0   |    
+----------+-------+-------+-------+-------+-------+

Here's the query I'm using:

select  concat(anu.FirstName,' ',anu.LastName) as Adjuster
    ,case s.stat when 'Pending Assignment' then count(a.clmNo) else 0 end as pending
    ,case s.stat when 'Assigned' then count(a.clmNo) else 0 end as assnd
    ,case s.stat when 'QA Ready' then count(a.clmNo) else 0 end as qardy
    ,case s.stat when 'In QA' then count(a.clmNo) else 0 end as inqa
    ,case s.stat when 'Closed' then count(a.clmNo) else 0 end as clsd
from assnmts a 
inner join assnmtStats astats 
on a.assnmtIdPk = astats.assnmtIdFk 
inner join stats s 
on astats.aStatId = s.statIdPk 
inner join repAssnmts ra 
on a.assnmtIdPk = ra.assnmtIdFk 
inner join aspnetusers anu 
on ra.repId = anu.Id 
inner join clients c 
on a.clientIdFk = c.clientIdPk 
inner join carrs 
on a.carrierId = carrs.carrIdPk 
inner join (SELECT a2.assnmtIdPk, MAX(astats2.asCrtdDt) as MaxDate FROM assnmts a2 INNER JOIN assnmtStats astats2 on a2.assnmtIdPk =   astats2.assnmtIdFk GROUP BY a2.assnmtIdPk) mdt 
on a.assnmtIdPk = mdt.assnmtIdPk 
and astats.asCrtdDt = mdt.MaxDate 
inner join (select a3.assnmtIdPk, MAX(ra2.raCrtdDt) as MaxRepDate from assnmts a3 inner join repAssnmts ra2 on a3.assnmtIdPk = ra2.assnmtIdFk group by a3.assnmtIdPk) mrepdt 
on a.assnmtIdPk = mrepdt.assnmtIdPk 
and ra.raCrtdDt = mrepdt.MaxRepDate 
group by concat(anu.FirstName,' ',anu.LastName), s.stat
order by concat(anu.FirstName,' ',anu.LastName)

How can I modify the query to get the results in the format needed?

Upvotes: 2

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use aggregation:

with t as (<your query here>)
select adjuster, sum(pending) as pending, sum(assnd) as assnd, 
       sum(qardy) as qardy, sum(inqa) as inqa, sum(clsd) as clsd
from t
group by adjuster;

Presumably, in your query, you can use conditional aggregation:

select concat(anu.FirstName, ' ', anu.LastName) as Adjuster
       sum(case s.stat when 'Pending Assignment' then a.clmNo else 0 end) as pending,
       sum(case s.stat when 'Assigned' then a.clmNo else 0 end) as assnd
       sum(case s.stat when 'QA Ready' then a.clmNo else 0 end) as qardy
       sum(case s.stat when 'In QA' then a.clmNo else 0 end) as inqa
       sum(case s.stat when 'Closed' then a.clmNo else 0 end) as clsd
from . . .
group by concat(anu.FirstName, ' ', anu.LastName);

Upvotes: 0

S3S
S3S

Reputation: 25112

Easy way would be to wrap the query and add your aggregate:

select 
    Adjuster
    ,sum(pending)
    ,sum(assnd)
    ,sum(qardy)
    ,sum(inqa)
    ,sum(clsd)
from(
select  concat(anu.FirstName,' ',anu.LastName) as Adjuster
    ,case s.stat when 'Pending Assignment' then count(a.clmNo) else 0 end as pending
    ,case s.stat when 'Assigned' then count(a.clmNo) else 0 end as assnd
    ,case s.stat when 'QA Ready' then count(a.clmNo) else 0 end as qardy
    ,case s.stat when 'In QA' then count(a.clmNo) else 0 end as inqa
    ,case s.stat when 'Closed' then count(a.clmNo) else 0 end as clsd
from assnmts a 
inner join assnmtStats astats 
on a.assnmtIdPk = astats.assnmtIdFk 
inner join stats s 
on astats.aStatId = s.statIdPk 
inner join repAssnmts ra 
on a.assnmtIdPk = ra.assnmtIdFk 
inner join aspnetusers anu 
on ra.repId = anu.Id 
inner join clients c 
on a.clientIdFk = c.clientIdPk 
inner join carrs 
on a.carrierId = carrs.carrIdPk 
inner join (SELECT a2.assnmtIdPk, MAX(astats2.asCrtdDt) as MaxDate FROM assnmts a2 INNER JOIN assnmtStats astats2 on a2.assnmtIdPk =   astats2.assnmtIdFk GROUP BY a2.assnmtIdPk) mdt 
on a.assnmtIdPk = mdt.assnmtIdPk 
and astats.asCrtdDt = mdt.MaxDate 
inner join (select a3.assnmtIdPk, MAX(ra2.raCrtdDt) as MaxRepDate from assnmts a3 inner join repAssnmts ra2 on a3.assnmtIdPk = ra2.assnmtIdFk group by a3.assnmtIdPk) mrepdt 
on a.assnmtIdPk = mrepdt.assnmtIdPk 
and ra.raCrtdDt = mrepdt.MaxRepDate 
group by concat(anu.FirstName,' ',anu.LastName), s.stat
) x
group by Adjuster

Upvotes: 3

Related Questions