CODEWITHSUNDEEP
sqldistinct
AlmostThere
AlmostThere

Reputation: 647

Distinct Count SQL

I tried searching this forum for an answer but could not find one that fit mine dilemma exactly.

I have a list of claims that can be in different statuses. I want a distinct count of claims where the status is open. The example below details three columns; Claim, ClaimLine, and Status

Claim | ClaimLine | Status
------+-----------+--------
    1 |         1 | Open
    1 |         2 | Open
    1 |         3 | Open
    2 |         1 | Enroute
    2 |         2 | Enroute
    3 |         1 | Closed
    4 |         1 | Open
    5 |         1 | Open
    5 |         2 | Open
    5 |         3 | Open

Desired Output:

Open
3

Upvotes: 1

Views: 259

Answers (4)

montelof
montelof

Reputation: 501

This way you don't have to group, a simplified version below this example :

select count(distinct claim),'Open' from (
select 1 as Claim, 1 as Claim_Line, 'Open' as Status 
union all 
select 1, 2, 'Open'
union all
select 1, 3, 'Open' 
union all 
select 2, 1, 'En-route' 
union all 
select 2, 2, 'En-route' 
union all 
select 3, 1, 'Closed' 
union all 
select 4, 1, 'Open' 
union all 
select 5, 1, 'Open'
union all 
select 5, 2, 'Open' 
union all 
select 5, 3, 'Open')sunquery  where status = 'Open'

simplified version:

select count(distinct claim),'Open' from Claims
where status = 'Open'

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

This should do it:

Sample Data:

CREATE TABLE #temp (Claim int , Claim_Line int , Status VARCHAR(20))
INSERT INTO #temp
VALUES
(1 ,1 ,'Open'), 
(1 ,2 ,'Open'), 
(1 ,3 ,'Open'), 
(2 ,1 ,'En-route'), 
(2 ,2 ,'En-route'), 
(3 ,1 ,'Closed'),
(4 ,1 ,'Open'), 
(5 ,1 ,'Open'), 
(5 ,2 ,'Open'), 
(5 ,3 ,'Open')

Query:

SELECT  Status, COUNT(DISTINCT Claim) FROM #temp 
WHERE Status = 'Open'
GROUP BY Status

Results:

enter image description here

Upvotes: 4

mojorisinify
mojorisinify

Reputation: 405

Something like this:

SELECT Status, COUNT(1) as Count FROM Claim 
WHERE Status = 'Open' GROUP BY Status

Upvotes: 0

Shultc
Shultc

Reputation: 214

You need to use COUNT() function with DISTINCT parameter.

So it will be something like that:

SELECT COUNT(DISTINCT Status) FROM 
  (SELECT * FROM table_name
   GROUP BY Claim)
WHERE Status = 'Open'
GROUP BY Status

Upvotes: 0

Related Questions