CODEWITHSUNDEEP
sqlsql-servert-sql
user1098028
user1098028

Reputation: 69

SQL query sum of total corresponding rows

I have two tables as below. Caseid from first table is referenced in second table along with accidents. What I am trying to get total different accidents for a case type. Below two tables I documented sample data and expected result.

Table case:

caseId     CaseType
 1            AB
 2            AB
 3            AB
 4            CD
 5            CD
 6            DE

Table CaseAccidents:

AccidentId   caseID    AccidentRating
1         1       High
2         1       High
3         1       Medium    
4         1       LOW
5         2       High
6         2       Medium    
7         2       LOW
8         5       High
9         5       High
10        5       Medium    
11        5       LOW

Result should look like:

CaseType TotalHIghrating    TotalMediumRating   TotalLOWRating
AB           3          2           2
CD           2          1           1
DE           0          0           0

Upvotes: 0

Views: 152

Answers (5)

Mahesh.K
Mahesh.K

Reputation: 901

Try This code once.

select casetype,
sum(case when ca.AccidentRating='High' then 1 else 0 end ) as TotalHIghrating,
sum(case when ca.AccidentRating='Medium' then 1 else 0 end ) as TotalMediumRating   ,
sum(case when ca.AccidentRating='Low' then 1 else 0 end ) as TotalLOWRating
from #case c
left join #CaseAccidents ca on c.caseid=ca.CaseId
group by casetype

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Another approach using Pivot operator 

SELECT casetype,
       [High],
       [Medium],
       [Low]
FROM   (SELECT c.casetype,
               AccidentRating
        FROM   case c
               LEFT JOIN CaseAccidents ca
                      ON ca.CaseId = c.caseid)a
       PIVOT (Count(AccidentRating)
             FOR AccidentRating IN ([High],
                                    [Medium],
                                    [Low]) ) p

Upvotes: 0

McNets
McNets

Reputation: 10807

To get the sum of every rating, you can Use a SUM(CASE WHEN) clause, adding 1 by every record that match the rating.

In your question, you have pointed out that you want to see all distinct CaseType, you can get it by using a RIGHT JOIN, this will include all records of case table.

select case.CaseType,
       sum(case when caseAccidents.AccidentRating = 'High' then 1 else 0 end) as TotalHighRating,
       sum(case when caseAccidents.AccidentRating = 'Medium' then 1 else 0 end) as TotalMediumRating,
       sum(case when caseAccidents.AccidentRating = 'LOW' then 1 else 0 end) as TotalLowRating
from caseAccidents
     right join case on case.caseId = caseAccidents.caseID
group by case.CaseType;

+----------+-----------------+-------------------+----------------+
| CaseType | TotalHighRating | TotalMediumRating | TotalLowRating |
+----------+-----------------+-------------------+----------------+
|    AB    |        3        |         2         |        2       |
+----------+-----------------+-------------------+----------------+
|    CD    |        2        |         1         |        1       |
+----------+-----------------+-------------------+----------------+
|    DE    |        0        |         0         |        0       |
+----------+-----------------+-------------------+----------------+

Check it: http://rextester.com/MCGJA9193

Upvotes: 2

Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

Please see this. Sample query of the table and also that result

create table #case(caseid int,casetype varchar(5))
  insert into #case (caseid,casetype)
   select 1,'AB' union all
   select 2,'AB' union all
   select 3,'AB' union all
   select 4,'CD' union all
   select 5,'CD' union all
   select 6,'DE' 

create table #CaseAccidents(AccidentId int, CaseId int,AccidentRating varchar(10))
insert into #CaseAccidents(AccidentId, CaseId, AccidentRating)
 select 1,1,'High' union all
 select 2,1,'High' union all
 select 3,1,'Medium' union all
 select 4,1,'Low' union all
 select 5,2,'High' union all
 select 6,2,'Medium' union all
 select 7,2,'Low' union all
 select 8,5,'High' union all
 select 9,5,'High' union all
 select 10,5,'Medium' union all
select 11,5,'Low'

My script

select c.casetype,
   sum(case when ca.AccidentRating='High' then 1 else 0 end) as TotalHighRating,
   sum(case when ca.AccidentRating='Medium' then 1 else 0 end) as TotalMediumRating,
   sum(case when ca.AccidentRating='Low' then 1 else 0 end) as TotalLowRating   
    from #case c
    Left join #CaseAccidents ca
    on c.Caseid=ca.Caseid
    group by c.casetype

Hope This could help!

Upvotes: 0

user3112728
user3112728

Reputation: 405

Have you use case in a select clause before?

select C.CaseType, 
sum(case when CA.AccidentRating = 'High' then 1 else 0 end)
from Case C join CaseAccidents CA on C.CaseId = CA.CaseId
group by C.CaseType

Upvotes: 0

Related Questions