Stored procedure get count

[i***@gmail.com]

I have a table CourtCase with following fields:
CourtCaseId (PK int)
CourtLevelId (FK int)
CourtId (FK int)
CaseNumber (varchar)
isDecided (bool)
isDeleted (bool)

I have another table CourtLevel with following fields
CourtlevelId, CourtTitle

I have a stored procedure which takes 2 parameters:
CourtLevelId, CourtId

I have to get the following data based on courtlevelid and courtid
CourtTitle, TotalCase

I need to get following details based on CourtlevelId and CourtId
Courttitle, TotalCases, Total Cases where isdecided=1, total cases where isdecided=1 and isdeleted=0

[Bob Barrows]

Post by i***@gmail.com
CourtCaseId (PK int)
CourtLevelId (FK int)
CourtId (FK int)
CaseNumber (varchar)
isDecided (bool)
isDeleted (bool)
I have another table CourtLevel with following fields
CourtlevelId, CourtTitle
CourtLevelId, CourtId
I have to get the following data based on courtlevelid and courtid
CourtTitle, TotalCase
I need to get following details based on CourtlevelId and CourtId
Courttitle, TotalCases, Total Cases where isdecided=1, total cases
where isdecided=1 and isdeleted=0

select Courttitle
, count(*) as TotalCases
, sum(case isDecided when 1 then 1 else 0 end) as TotalDecided
, sum(case when isDecided =1 and isdeleted=0 then 1 else 0 end) as
TotalDecidedDeleted
from CourtCase as c
join CourtLevel as l on c.CourtLevelId = l.CourtLevelId
where c.CourtLevelId = @CourtLevelId and CourtId = @CourtId
group by Courttitle

[i***@gmail.com]

This query works just fine. However it returns only one courtleveltitle. More than one courtleveltitle exist for @courtid and @courtlevelid

select 1 as courtlevel,l.courtleveltitle, 'abc' as url, COUNT(*) as total, SUM(case isdecided when 1 then 1 else 0 end)as decided, SUM(case isdecided when 0 then 1 else 0 end)as pending, SUM(case isdecided when 1 then 1 else 0 end)-SUM(case when c.isdecided=1 and right(c.DecisionOrderFile,1)='/' then 1 else 0 end) as uploadfrom CourtCase as c join CourtLevels as l on c.CourtLevelID=l.CourtLevelID where c.CourtLevelID=@CourtLevelid and c.CourtID=@CourtId group by l.courtleveltitle

[Bob Barrows]

Post by i***@gmail.com
This query works just fine. However it returns only one
@courtlevelid
select 1 as courtlevel,l.courtleveltitle, 'abc' as url, COUNT(*) as
total, SUM(case isdecided when 1 then 1 else 0 end)as decided,
SUM(case isdecided when 0 then 1 else 0 end)as pending, SUM(case
isdecided when 1 then 1 else 0 end)-SUM(case when c.isdecided=1 and
right(c.DecisionOrderFile,1)='/' then 1 else 0 end) as uploadfrom
CourtCase as c join CourtLevels as l on c.CourtLevelID=l.CourtLevelID
l.courtleveltitle

Please show sample data in tabular format followed by results desired from
that sample data, also in tabular format.

[Erland Sommarskog]

Post by i***@gmail.com
This query works just fine. However it returns only one courtleveltitle.
select 1 as courtlevel,l.courtleveltitle, 'abc' as url, COUNT(*) as total,
SUM(case isdecided when 1 then 1 else 0 end)as decided,
SUM(case isdecided when 0 then 1 else 0 end)as pending,
SUM(case isdecided when 1 then 1 else 0 end)-
SUM(case when c.isdecided=1 and right(c.DecisionOrderFile,1)='/'
then 1 else 0
end) as upload
from CourtCase as c
join CourtLevels as l on c.CourtLevelID=l.CourtLevelID
group by l.courtleveltitle

So what is the primary key of CourtLevels? It's difficult to assist with a
problem like this without table definitions and sample data. But if I should
work from the names, I would assume that CourtLevelID is the PK of
Courtlevels, and in that case, there can only be one court-level title.