How to count specific values from table

Question

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

What would really help is some ddl, sample data and the desired output for that sample data. From the gibberish posted above it is impossible to tell what is in what column. Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Dec 15, 2017 at 14:06

Vash · Accepted Answer · 2017-12-14 21:46:22Z

1

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.

answered Dec 14, 2017 at 21:46

Vash

1,7972 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Avi22 Over a year ago

hi, I have checked it but query retunes no values after executing.

Vash Over a year ago

What are possible values of MIR_ID? I am not able to figure out from your post.

Avi22 Over a year ago

my mistake it return values but not counting according to MIR_ID.

Vash Over a year ago

Are you storing the results in a table before running the COUNT(*)? If so, try verifying the table contains the results as expected.

Avi22 Over a year ago

only numbers... for example after query executing I want to see that MIR_ID "335403" appears 5 times and like this for all others MIR_ID numbers.

|

Collectives™ on Stack Overflow

How to count specific values from table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related