Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>SQL is easier to understand inside out.</p> <blockquote> <p>The problem is to identify the percentage of times that 'Attribute6' is populated for the set of rows when there are more than 1 value for 'Attribute5' when 'Attibute1' to 'Attribute4' were the same. </p> </blockquote> <p>Breaks down like this:</p> <ol> <li>Where attributes 1-4 are the same</li> <li>And there is more than one value for attribute5</li> <li>Give the percentage of times Attribute6 is populated</li> </ol> <p>Like so:</p> <pre><code>select attribute1, attribute2, attribute3, attribute4, -- 3. give percentage of times Attribute6 is populated -- Percentage is numerator * 100 over denominator -- 3.a. Numerator: Number of times attribute 6 is populated sum( case when attribute6 is null then 0 else 1 end) * 100 / -- 3.b. Denominator: Total number of attribute5 found count(attribute5) from Problem p -- 1. where attributes 1-4 are the same group by attribute1, attribute2, attribute3, attribute4 -- 2. And there is more than one value for attribute5 having count(distinct attribute5) &gt; 1 </code></pre> <p>You haven't been clear on the definiton of "attribute5 has more than one value" - I have assumed you mean more than one distinct value. If you just meant "not null" that is easy too - just replace the count(distinct) with the appropriate expression to get what you want.</p> <h3>Edit</h3> <p>With the added clarity that we are looking for a single number, which is the percentage of groups where there are multiple distinct values of Attribute5, which also have multiple values of attribute6.</p> <p>It's not clear how you want to handle nulls and empty strings, so I am assuming that there are no nulls and empty strings count as a normal value.</p> <p>Try the following:</p> <pre><code>select sum(nDistinct5) as nDemoninator, sum(nDistinct6) as nNumerator, sum(nDistinct6) * 100.0 / sum(nDistinct5) from ( select attribute1, attribute2, attribute3, attribute4, -- 3. give percentage of times Attribute6 is populated -- Percentage is numerator * 100 over denominator -- 3.a. Numerator: Number of times attribute 6 is populated count(distinct attribute6) as nDistinct6, -- 3.b. Denominator: Total number of attribute5 found sum(1) as nDistinct5 from Problem p -- 1. where attributes 1-4 are the same group by attribute1, attribute2, attribute3, attribute4 -- 2. And there is more than one value for attribute5 having count(distinct attribute5) &gt; 1 ) g </code></pre> <p>For eyeballing purposes, join the original data onto the subquery g so you can manually confirm the logic is correct.</p> <pre><code>select p.*, g.nDistinct6, g.nDistinct5 from ( select attribute1, attribute2, attribute3, attribute4, -- 3. give percentage of times Attribute6 is populated -- Percentage is numerator * 100 over denominator -- 3.a. Numerator: Number of times attribute 6 is populated count(distinct attribute6) as nDistinct6, -- 3.b. Denominator: Total number of attribute5 found sum(1) as nDistinct5 from Problem p -- 1. where attributes 1-4 are the same group by attribute1, attribute2, attribute3, attribute4 -- 2. And there is more than one value for attribute5 having count(distinct attribute5) &gt; 1 ) g right outer join Problem p on p.attribute1 = g.attribute1 and p.attribute2 = g.attribute2 and p.attribute3 = g.attribute3 and p.attribute4 = g.attribute4 order by p.attribute1, p.attribute2, p.attribute3, p.attribute4 </code></pre> <p>This displayes every row from <code>Problem</code>, and the corresponding group totals for number of distinct <code>Attribute6</code> and <code>Attribute5</code>, so you can validate that these are indeed the numbers you want to use. If there are too many rows and you just want to eyeball a few hundred, you can use <code>top</code></p>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server 2008: How to get a Detail ID when doing a multi-column GROUP BY and HAVING
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBen
    1. USBen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL Server 2008: How to get a Detail ID when doing a multi-column GROUP BY and HAVING
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COYes, you are correct, I meant more than one distinct value. I will try this later - it looks wonderfully simple.
      singulars
      1. PO
      1. USScott
    2. COI added some demo data, and I tried to clarify that I need all of the times that 'Attribute6' is populated for the entire set of rows when there are more than 1 value for 'Attribute5' when 'Attibute1' to 'Attribute4' were the same. Your soultion (which I appreciate greatly and will use as a starting piont after the morning meeteings) calculates a percentage for each grouping of attributes 1-4.
      singulars
      1. PO
      1. USScott
    3. COI like this approach, but we the real problem (hence the title) that I am struggling with is to obtain the UniqueId for each row in the sample frame (the divisor) so I can validate these figures. As soon as I introduce a GROUP BY I cannot include the UniqueID...
      singulars
      1. PO
      1. USScott
 

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