Databases
StackOverflow2013
Postsdbo
POHow to get Sum of Column, Where another column has Count = 1
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get Sum of Column, Where another column has Count = 1
    text
    copied!<p>Sample Data:</p> <pre><code>+===========================================================================+ |NoBoxes | Carrier | ProcessDateTime | Errored | Voided | TrackingNumber | +===========================================================================+ | 2 | UPS | 5/22/2013 8:14 | 0 | 0 | 1Z1234567891234567| +---------------------------------------------------------------------------+ | | UPS | | 0 | 1 | 1Z1234567891234567| +---------------------------------------------------------------------------+ | 5 | UPS | 5/22/2013 8:22 | 1 | 0 | | +---------------------------------------------------------------------------+ | 7 | UPS | 5/22/2013 8:14 | 0 | 0 | 1Z9876543210987654| +---------------------------------------------------------------------------+ | 1 | UPS | 5/22/2013 8:22 | 0 | 0 | 1Z1472583691472583| +---------------------------------------------------------------------------+ | 1 | FedEx | 5/22/2013 8:14 | 0 | 0 | xxxxxxxxxxxxxxxx | +---------------------------------------------------------------------------+ | 8 | FedEx | 5/22/2013 8:22 | 0 | 0 | yyyyyyyyyyyyyyyy | +---------------------------------------------------------------------------+ | 3 | USPS | 5/22/2013 8:14 | 0 | 0 | zzzzzzzzzzzzzzzz | +---------------------------------------------------------------------------+ | 4 | USPS | 5/22/2013 8:22 | 0 | 0 | aaaaaaaaaaaaaaaa | +---------------------------------------------------------------------------+ | 7 | UPS | 5/22/2013 8:14 | 0 | 0 | 1Z9638527411012396| +---------------------------------------------------------------------------+ | 9 | UPS | 5/22/2013 8:22 | 0 | 0 | 1Z4561591981655445| +---------------------------------------------------------------------------+ </code></pre> <p>Now with a table like this, how can I get the sum of <code>NoBoxes</code> Where <code>Carrier</code> = UPS, <code>ProcessDateTime</code> = Today, <code>Errored</code> = 0, and <code>TrackingNumber</code> Having Count = 1? </p> <p>The duplicate Tracking Numbers represent a shipment that was voided. I don't want to sum <code>Errored</code> shipments as those didn't ship. </p> <p>I have tried about 10 different statements and nothing can seem to get me where I need to be. </p> <p>The issue I <strong><em>Believe</em></strong> is that the voided rows do not contain a <code>ProcessDateTime</code>. </p> <p>So when I use something like: </p> <pre><code>SELECT Sum(NoBoxes) FROM Info WHERE (Carrier='UPS') AND (ProcessDateTime&gt;{ts '2013-05-23 00:00:00'} And ProcessDateTime&lt;{ts '2013-05-24 00:00:00'}) AND (Errored=0) GROUP BY TrackingNumber HAVING (Count(*)=1) </code></pre> <p>It still returns <code>TrackingNumber</code> that have been voided because the query doesn't contain any rows with null <code>ProcessDateTime</code></p> <p>so then I tried: </p> <pre><code>SELECT Sum(NoBoxes), ProcessDateTime FROM Info WHERE ((Carrier='UPS') AND (Errored=0)) OR ((Carrier='UPS') AND (ProcessDateTime Is Null) AND (Errored=0)) GROUP BY ProcessDateTime, TrackingNumber HAVING (Count(*)=1) </code></pre> <p>But this still doesn't do the job it just returns everything. </p> <p>Also tried <code>HAVING (Count(TrackingNumber)=1)</code> but didn't seem that did anything. </p> <p>I just can't figure out how to get rid of all duplicate tracking numbers, then return the sum of all <code>UPS</code> with a <code>ProcessDateTime</code> value within criteria. Because it seems the only way to get rid of the duplicates is to not use <code>ProcessDateTime</code> at all, but then I have no way of telling what the <code>ProcessDateTime</code> is, and I need to know in order to filter for specific date. </p> <p>I kind of understand that I will probably have to do something like: </p> <pre><code>(Select TrackingNumber From Info Where Carrier = 'UPS' And Errored = 0 Group By TrackingNumber HAVING Count(*) = 1) As A </code></pre> <p>Then do something along the lines of: </p> <pre><code>Select Sum(NoBoxes) As Total From Info Join A On Info.TrackingNumber = A.TrackingNumber Where Info.ProcessDateTime&gt;{ts '2013-05-23 00:00:00'} And Info.ProcessDateTime&lt;{ts '2013-05-24 00:00:00'} </code></pre> <p>But I am simply not knowledgeable about this to get all the right order and syntax correct on such a Query. </p> <p>Given the table provided I would like a single Row returned with a single Column with the Value of <code>24</code></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