This is my Query. But its not working as expected.Count of Hard bounce list retrieved using Query is different from tracking report hard bounce list :
select
E.[Email]
from
Test_DE E
inner join
_bounce B
on
B.SubscriberKey = E.[SubscriberKey]
where
B.BounceCategory = 'Hard bounce'
Moving from Comment to Answer to make it easier to find solution:
I am assuming as you mention tracking report that this is specific to a single send job. This means your count would be off because the _Bounce DV is across any and every bounces this SubscriberKey has ever had. Meaning the counts would not match unless you limit the DV to just that job as well.
Essentially, the tracking numbers you see in the tracking report are aligned to just that single send job that was executed. This means that it will only show a hard bounce occuring if it happened in that context.
BUT the Bounce Dataview is all bounces across all send jobs for the past 6 months. This means that a subscriber can have multiple hard bounces here due to the different send job contexts and other variables.
So trying to compare the tracking number to the dataview number from your query is similar to trying to find out why in your fridge you only have 1 popsicle, but when you go to the grocery store, you can find dozens of them.
So, to compare apples to apples, you would want to add a condition to your SQL to target just that job (usually JobID) and that should align contexts.
