SELECT EMO.Callsign, 
SUM(CASE WHEN (VSO.ShooterBOI < 10000) 
          AND (VSO.TargetBOI BETWEEN 20000 AND 20500) 
          AND (VSO.DetonationResult = 1) 
          AND (TAO.MissionType IN ('D', 'O', 'S')) 
          THEN 1 ELSE 0 END) AS Expr1,

(select count(*) 
from IntegratedTest1.dbo.ValidShots  as VS 
INNER JOIN Config.TrialAbstract as TA ON VS.TrialID =TA.TrialID 
where (VS.ShooterBOI =VSO.ShooterBOI) 
AND (VS.DetonationResult = VSO.DetonationResult)	 AND (TA.MissionType = TAO.MissionType)) as Expr2
                   
FROM IntegratedTest1.dbo.ValidShots as VSO 
INNER JOIN Config.TrialAbstract as TAO 
  ON VSO.TrialID = TAO.TrialID 
INNER JOIN IntegratedTest1.TrialConfig.EntityMap as EMO 
  ON VSO.TargetBOI = EMO.BOI

WHERE VSO.ShooterBOI < 10000 
AND VSO.DetonationResult = 1 
AND TAO.MissionType in ('O', 'D','S')
GROUP BY EMO.Callsign,VSO.ShooterBOI,VSO.DetonationResult,TAO.MissionType

Hello all. I am having trouble with the query displayed above. The reason that I am using the sub-query is because I don't want the results of an aggregate function to be constrained by the Where clause in the main query. But the results of the sub-query are not returning what is expected. The result is always 2, which is not correct. I thought that by using

WHERE        (VS.ShooterBOI = IntegratedTest1.dbo.ValidShots.ShooterBOI) AND (VS.DetonationResult = IntegratedTest1.dbo.ValidShots.DetonationResult) AND
                                                         (TA.MissionType = Config.TrialAbstract.MissionType)) AS Expr2

that it would return results based on the current record that the main query was processing. Does this all make sense? Any help would be appreciated.

Edit1: Optimized code as suggested, but still getting same results.

I want the Inner Query to basically match the outer query as far as joins go except for the Entity Map table. But the where clause is different. I want the Where clause in the inner query to get it's values from the outer query for each record that the outer query produces. Does that make sense?

Edit 2: I figured out what I needed to do. Instead of using a sub-query, I decided to create a User Defined Function:

Use Metadata
Go
IF OBJECT_ID (N'Event.XXX', N'FN') IS NOT NULL
    DROP FUNCTION XXX;
GO
CREATE FUNCTION Event.XXX(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(CASE WHEN IntegratedTest1.dbo.ValidShots.ShooterBOI = @ProductID 
							AND IntegratedTest1.dbo.ValidShots.TargetBOI < 10000 
							AND IntegratedTest1.dbo.ValidShots.DetonationResult = 1 
							AND Config.TrialAbstract.MissionType IN ('D', 'O', 'S') 
                         THEN 1 ELSE 0 END)
FROM IntegratedTest1.dbo.ValidShots  
INNER JOIN Config.TrialAbstract ON IntegratedTest1.dbo.ValidShots.TrialID = Config.TrialAbstract.TrialID 
INNER JOIN IntegratedTest1.TrialConfig.EntityMap AS EMO ON IntegratedTest1.dbo.ValidShots.TargetBOI = EMO.BOI        
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

And I am calling the function from my original query:

SELECT EMO.Callsign, SUM(CASE WHEN (VSO.ShooterBOI < 10000) AND (VSO.TargetBOI BETWEEN 20000 AND 20500) AND (VSO.DetonationResult = 1) AND (TAO.MissionType IN ('D', 'O', 'S')) THEN 1 ELSE 0 END) AS Expr1,
Event.XXX(VSO.TargetBOI) as Expr2
                   
FROM IntegratedTest1.dbo.ValidShots as VSO INNER JOIN
Config.TrialAbstract as TAO ON VSO.TrialID = TAO.TrialID INNER JOIN IntegratedTest1.TrialConfig.EntityMap as EMO ON VSO.TargetBOI = EMO.BOI
WHERE VSO.ShooterBOI < 10000 AND VSO.DetonationResult = 1 AND TAO.MissionType in ('O', 'D','S')
GROUP BY EMO.Callsign,VSO.ShooterBOI,VSO.DetonationResult,TAO.MissionType,VSO.TargetBOI

Thanks to all of you for your suggestions.

Thanks,

David

• Edited by David_W_1969 11 hours 22 minutes ago

Can you explain your problem better? You don't have to use a subquery at all, you can use COUNT() with correct partition inside the main query.

I would start from making short aliases for all tables involved in the query to make this query readable. Also, your WHERE condition doesn't look correct to me as the mixture of AND and OR. I suspect you need

(someCondition and anotherCondition and 3rd Condition) OR 

(1st Cond and 2nd Cond and 3rd Cond)

In this way your intent is clear. The way the query is currently written - not clear at all.

So, please re-write your query using these points and if you still will have troubles come back with the fixed query which will be easier to fix for us too.

Thank you. I will do that.

Hi,

as far as I understand what you expect from you inner subquery, you should have defined a table alias in you outer query, and reuse this alias (without a re-definition) in the inner one.

please try it an tell us.

Sbastien

I have optimized the entire query and edited my post. Hopefully it will be easier to understand now. I appreciate your help.

Thanks Sebastian. I have created the alias's for the outer query tables, but I still get the same results. I have edited my original post to show the new query. Thanks for your help.

Try if your condition is not too restrictive:

;with cte as (SELECT EMO.CallSign, 
VSO.ShooterBOI, TA.MissionType, VSO.DetonationResult,
SUM(CASE WHEN (VSO.ShooterBOI < 10000) 
          AND (VSO.TargetBOI BETWEEN 20000 AND 20500) 
          AND (VSO.DetonationResult = 1) 
          AND (TAO.MissionType IN ('D', 'O', 'S')) 
          THEN 1 ELSE 0 END) AS cntSpecific,

count(*) as TotalCount                   
FROM IntegratedTest1.dbo.ValidShots as VSO 
INNER JOIN Config.TrialAbstract as TAO 
  ON VSO.TrialID = TAO.TrialID 
INNER JOIN IntegratedTest1.TrialConfig.EntityMap as EMO 
  ON VSO.TargetBOI = EMO.BOI
GROUP BY EMO.Callsign,VSO.ShooterBOI,VSO.DetonationResult,
TAO.MissionType)

select * from cte 
WHERE ShooterBOI < 10000 
AND DetonationResult = 1 
AND MissionType in ('O', 'D','S')

BTW, it is easy to understand why your subquery is not working as you would like. You're joining on DetonationResult and MissionType and you're filtering in the main query. So, only rows which will be filtered in the main query will be joined. In other words, you can not get the result you want using subquery. You need to do counts first, then apply where condition and even with this you may not get the result, as you're grouping by DetonationResult and MissionType and then excluding the unwanted. You may want to show some of your data and desired result to get bet