StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>After some wriggling, I came up with this beauty containing no <code>CASE</code> expression:</p> <pre><code>SELECT GA.GroupAttributeID AS AttributeID, GA.Name, COUNT(MA.AttributeID) AS AttributeCount FROM GroupAttribute AS GA LEFT OUTER JOIN @MemberAttribute AS MA ON GA.GroupAttributeID = MA.AttributeID AND MA.AttributeValue = 'True' GROUP BY GA.GroupAttributeID, GA.Name </code></pre> <p>This takes advantage of the fact that if there are no 'True' values for a particular <code>AttributeID</code>, the <code>MA.AttributeID</code> resulting from the <code>LEFT OUTER JOIN</code> will be <code>NULL</code>. The <code>NULL</code> value passed into <code>COUNT()</code> will lead to an <code>AttributeCount</code> of zero. The <code>LEFT OUTER JOIN</code> also ensures that a row will be present in the result set for <code>AttributeID</code> rows with zero counts.</p> <p>The assumption with this query is that all group attributes are represented in the <code>@MemberAttribute</code> table variable. If not, there will be rows with zero counts representing those group attributes that are absent. If this is undesirable, a <code>WHERE</code> clause can be added to filter them out, complicating this query. Will's solution(s) would be far more practical if this is the case.</p> <p>The execution plan compares well with Will's first solution, containing one less (Compute Scalar) step. It does use a <code>LEFT OUTER JOIN</code> vs an <code>INNER JOIN</code>, however, making the two methods practically identical for this simple example. It would be interesting to see how the two solutions scale if table variable is converted to a fairly large table, instead.</p> <p>Will's actual plan for his solution involving <code>COUNT()</code>:</p> <p><img src="https://i.stack.imgur.com/YpCSu.png" alt="enter image description here"></p> <p>My actual plan:</p> <p><img src="https://i.stack.imgur.com/3Ct3N.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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