CODEWITHSUNDEEP
sqlsql-servercount
Avi22
Avi22

Reputation: 57

How to count specific values from table

I have below query that give my the next results (below).

I would like to know how can I count the "MIR_ID" that appears more than once only from query results (if it possible).

Thanks!

My query: 

with Source as (
select 
request_id
,description
,substring(VwDurationIndicator.RequestDetails, 5, 6) as Mir_ID
, request_type

from vortex_hvc.dbo.VwDurationIndicator 
 left join 
 vw_public_request_material_location_mir 
 on substring(VwDurationIndicator.RequestDetails, 5, 6) like vw_public_request_material_location_mir.material_request_id
where DateDiff(hour, CreatedTime, getdate()) < 9 and
 request_type = 'MIR' and SITE = 'HVC' 

        )
 select Source.*

 ,count(Mir_ID) as total
 from Source

 group by Mir_ID,request_id,description,request_type

 order by request_id

Query return:

request_id  description Mir_ID  request_type    total
152788  VPO: Y7363040RS, Step: PPV-M, Bin: BIN 01   335403  MIR 2
152788  VPO: Y7242580, Step: PPV-S FUSE, Bin: BIN 01    335403  MIR 1
152788  VPO: Y7243570, Step: PPV-S, Bin: BIN 01 335403  MIR 1
152788  VPO: Y7243580, Step: PPV-S, Bin: BIN 01 335403  MIR 1
152788  VPO: Y7322650, Step: PPV, Bin: BIN 01   335403  MIR 5
152788  VPO: Y7363020, Step: PPV-M, Bin: BIN 01 335403  MIR 2
152791  VPO: Y7324440, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324550, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324480, Step: OLB, Bin: BIN 2571 335412  MIR 2
152791  VPO: Y7324590, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324540, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324580, Step: OLB, Bin: BIN 2571 335412  MIR 3
152791  VPO: Y7324430, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324660, Step: OLB, Bin: BIN 2571 335412  MIR 1
152791  VPO: Y7324470, Step: OLB, Bin: BIN 2571 335412  MIR 1
152792  VPO: Y7342740, Step: PRODUCTION BURN-IN, Bin: BIN 01    335419  MIR 1
152792  VPO: Y7342730, Step: PRODUCTION BURN-IN, Bin: BIN 01    335419  MIR 1
152794  VPO: Y7324480, Step: OLB, Bin: BIN 2571 335420  MIR 1
152794  VPO: Y7324540, Step: OLB, Bin: BIN 2571 335420  MIR 1
152794  VPO: Y7324420, Step: OLB, Bin: BIN 2571 335420  MIR 2

Upvotes: 0

Views: 54

Answers (1)

Vash
Vash

Reputation: 1787

This should do the job: 

SELECT MIR_ID, COUNT(*) AS COUNT_MIR_ID
FROM YOUR_RESULT
GROUP BY MIR_ID
HAVING COUNT(*) > 1;

Assuming YOUR_RESULT contains the result you provided in the post. You can either store your result in a table OR use this query outside your original query.

Upvotes: 1

Related Questions