Is it correct to use Count () and Sum () in SQL?

Question

Good, so I hope that I can explain this question well enough, because I feel that it will be difficult.

I have two tables that I work with today. They look like this:

@pset table (PersonID int, SystemID int, EntitlementID int, TargetID int) @Connector table (TargetName varchar(10), fConnector bit) 

The first table contains records that tell me, oh, this person has this system, which consists of these rights, which have these goals. A bit complicated, but stay with me. The second stores the name TargetName, and then whether this target has a connector in my non-theoretical system.

What I'm trying to do is combine these two tables so that I can see the target flag for each row in @pset. This will help me later, as you will see.

If every right in the system has a connector to the target (the flag is true for all of them), then I would like to know.

Everyone else should go to another table.

This is what I tried to do, but it did not work. I need to know where I was wrong. Hopefully someone with more experience than me can answer.

 -- If the count(123) = 10 (ten rows with SystemID = 123) and the sum = 10, cool. select pset.*, conn.fConnector from @pset pset inner join vuTargets vt on vt.TargetID = pset.TargetID inner join @conn conn on conn.TargetName = vt.TargetName group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector having count(SystemID) = sum(cast(fConnector as int)) order by ProfileID 

and

 -- If the count(123) = 10 (ten rows with SystemID = 123) and the sum <> 10 select pset.*, conn.fConnector from @pset pset inner join vuTargets vt on vt.TargetID = pset.TargetID inner join @conn conn on conn.TargetName = vt.TargetName group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector having count(SystemID) <> sum(cast(fConnector as int)) order by ProfileID 

Unfortunately, they do not work :(

Edit

Here is a screenshot showing the problem. Notification. ProfileID 1599 has a system identifier of 1126567, but one of the rights does not have a connector! How can I get both of these lines in a second query? (Above)

Answer 1

The main problem is that you are trying to collapse to two different sets of records.
The initial set ( SELECT and GROUP BY clauses) means that for each difference in the set one record is required [ProfileId, SystemId, EntitlementId, TargetId, fConnector].
The second set (the HAVING ) says that you want each row in the set to compare its COUNT records with SUM connections by default. However, since you asked to group up to a separate flag, this results in one row for each flag (assuming a one-to-one relationship). Effectively, you say: "Hey, if this goal has a connection? Yes, I want it."

What you think is necessary is to collapse to the SystemId value. To do this, you will need to modify your SELECT and GROUP BY clauses to include only the [ProfileId, SystemId] set. This will return only those rows (with the key from the profile and the system) for which all targets are “connected”. You will not be able to see individual rights, goals and their relationship (you will be able to conclude that they will all be / are not related).

---

EDIT:

In the interest of full disclosure, here's how you get something similar to your original result set, which lists all EntitlementId and TargetId s:

 WITH all_connections as (SELECT pset.ProfileId, pset.SystemRoleId FROM @pset pset INNER JOIN vuTargets vt ON vt.TargetId = pset.TargetId INNER JOIN @conn conn ON conn.TargetName = vt.TargetName GROUP BY pset.ProfileId, pset.SystemRoleId HAVING COUNT(pset.SystemRoleId) = SUM(CAST(fConnector as INT))) SELECT pset.* FROM @pset pset JOIN all_connections conn ON conn.ProfileId = pset.ProfileId AND conn.SystemRoleId = pset.SystemRoleId 

This will lead you to the TargetId list, ProfileId / SystemRoleId , where all EntitlementId and TargetId have a connection (or flip CTE = to <> for those where not everyone does).

Answer 2

Edit: my initial requests are fixed, description is also updated

You can share this: first find a TargetID that has fConnector of 0. Then find the pairs PersonID , PersonID that have any target equal to those you found. Then select the appropriate data: (this finds a pair of PersonID , PersonID , where at least one right does not have a connector for the purpose)

 with abc as ( select PersonID, SystemID from pset P where TargetID in ( select TargetID from vuTargets V join connector C on V.TargetName = C.TargetName where C.fConnector = 0 ) ) select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector from pset P join abc on ((P.PersonID = abc.PersonID) and (P.SystemID = abc.SystemID)) join vuTargets V on P.TargetID = V.TargetID join connector C on V.TargetName = C.TargetName 

The search request for PersonID , PersonID pairs, where all rights have a connector for the purpose, is similar:

 with abc as ( select PersonID, SystemID from pset P where TargetID in ( select TargetID from vuTargets V join connector C on V.TargetName = C.TargetName where C.fConnector = 0 ) ) select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector from pset P join abc on ((P.PersonID <> abc.PersonID) or (P.SystemID <> abc.SystemID)) join vuTargets V on P.TargetID = V.TargetID join connector C on V.TargetName = C.TargetName 

The difference is in joining the temporary table ( <> vs = ). This is very similar to a null answer, but does not use calculations or sums.