Databases
StackOverflow2013
Postsdbo
POCount records in same column with and without parameter
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCount records in same column with and without parameter
    text
    copied!<p>I am having some trouble with a query and was hoping someone could help. I have tried searching for a solution, but can't seem to find a similar scenario with my search terms.</p> <p>Here is what I am looking for:</p> <p>I have a table with three columns and need to return the values in the first column, and two counts of the values in the second column. One count is based on a parameter and the other is the total count. Ideally, I would only like to return the values in column one that have equal counts.</p> <p>For example:</p> <pre><code>Part Number | Make ID ABC123 | 1&lt;br&gt; ABC123 | 1&lt;br&gt; ABC123 | 3&lt;br&gt; DEF456 | 1&lt;br&gt; DEF456 | 1 Part Number | Count of Apps Where Make ID = 1| Count of Total Apps ABC123 | 2 | 3 DEF456 | 2 | 2 </code></pre> <p>The query I have so far will return the total count of apps for part numbers that have the parameter value, but I need it to return both counts:</p> <pre><code>SELECT apps.part#, COUNT(DISTINCT application#)apps FROM [mytable] AS apps INNER JOIN (SELECT part# FROM [mytable] WHERE make = '1') AS sz ON sz.part# = apps.part# GROUP BY apps.part# ORDER BY 1 </code></pre> <p>Any help is greatly appreciated!</p> <p>Thanks, everyone! I received several correct answers, and selected the one that was give first:</p> <pre><code>SELECT part#, COUNT(DISTINCT CASE WHEN make = '1' THEN application# END) make1_apps, COUNT(DISTINCT application#) total_apps FROM [mytable] GROUP BY part# HAVING COUNT(DISTINCT CASE WHEN make = '1' THEN application# END) = COUNT(DISTINCT application#) ORDER BY part# </code></pre> <p>Thanks again!</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