Databases
StackOverflow2013
Postsdbo
POUsing Count() and Sum() correctly in SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing Count() and Sum() correctly in SQL?
    primarykey
    data
    text
    <p>Ok, so I hope I can explain this question well enough, because I feel like this is going to be a tough one.</p> <p>I have two tables I'm working with today. These look like:</p> <pre><code>@pset table (PersonID int, SystemID int, EntitlementID int, TargetID int) @Connector table (TargetName varchar(10), fConnector bit) </code></pre> <p>The first table stores records that tell me, oh this person has this system, which is composed of these entitlements, whom have these targets. A little complicated, but stay with me. The second stores the TargetName and then whether or not that target has a connector in my not-so-theoretical system.</p> <p>What I'm trying to do is merge these two tables so that I can see the target flag for each row in @pset. This will help me later as you'll see.</p> <p>If each entitlement in a system has a connector to the target (the <strong>flag is true</strong> for all of them), then I'd like to know. </p> <p>All the others should go into a different table. </p> <p>This is what I tried to do, but it didn't work. I need to know where I went wrong. Hopefully someone with more experience than me will be able to answer.</p> <pre><code>-- 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 </code></pre> <p>and</p> <pre><code>-- If the count(123) = 10 (ten rows with SystemID = 123) and the sum &lt;&gt; 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) &lt;&gt; sum(cast(fConnector as int)) order by ProfileID </code></pre> <p>Unfortunately, these do not work :(</p> <p><strong>Edit</strong></p> <p><img src="https://i.stack.imgur.com/scTYy.gif" alt="enter image description here"></p> <p>Here is a screenshot showing the problem. Notice ProfileID 1599 has a SystemID of 1126567, but one of the entitlements doesn't have a connector! How can I get both of these rows into the second query? (above)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTommy Fisk
    1. USTommy Fisk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou are using columns in your queries that are not in your provided schema, such as `TargetObjectID`. Please post the correct schema.
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. USRedFilter
    2. COSorry, I tried to sanitize the query but missed that, should be good now.
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. USTommy Fisk
    3. COso you want to find the `PersonId` and `SystemId` where every entitlement has all targets with an `fConnector` equal to 1?
      singulars
      1. POUsing Count() and Sum() correctly in SQL?
      1. USvlad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload