Plz Solve this sql Query..

Question

1) My Table: Query: select mcdespmcidnomcopstshascode from machine Table: mcdesp mcidno mcopsts hascode A A0001 URWT #### A A0001 GOOD #### A A0001 GOOD #### B A0002 URWP #### B A0002 URWP #### C A0003 GOOD #### C A0004 URWT #### Here i'm generating my hashcode using this script hascode = HASHBYTES('MD5' CAST(mcidno AS VARBINARY(MAX))+ CAST(mcdesp AS VARBINARY(MAX))+CAST(mcopsts AS VARBINARY(MAX))) Note: Same Color represents Hascodes are same. 2) My Query and its output: select mcdespcount(distinct hascode)as TotalMachine from machine group by mcdesp mcdesp TotalMahine A 2 B 1 C 2 Let This Tableas Tbl_Main What is the Query for finding Good Machines from my Tbl_Main?? that is mcopsts='good'. based on Hascode. I need a Result Like mcdesp GOOD A 1 B 0 C 1

Iftikar Hussain · Answer

Hi You can do like below select mcdespMAX(GoodCount) as Good from ( select mcdespcase when mcopsts='GOOD' THEN 1 ELSE 0 END as GoodCount from machine group by mcdespmcopstshascode ) as Data group by mcdesp Regards Iftikar Remember to click Mark as Answer on the post if it helps you.

Venkatesan · Answer

A A0001 GOOD #### A A0001 GOOD #### c A0003 GOOD #### so A has 2 good machines B has 0 Good machines C has one Good machine. Now when we compared A with Hascode. it has same hascode. so distinct it.

Pankaj Pandey · Answer

in last table what is 1 0 and 1?

mcdesp	mcidno	mcopsts	hascode
A	A0001	URWT	####
A	A0001	GOOD	####
A	A0001	GOOD	####
B	A0002	URWP	####
B	A0002	URWP	####
C	A0003	GOOD	####
C	A0004	URWT	####

mcdesp	TotalMahine
A	2
B	1
C	2

mcdesp	GOOD
A	1
B	0
C	1