Conditional count in query

[jbr87]

Hi guys and girls,



I posted this in the general forum but realized that this is the more apropriate forum for this discussion.

I'm looking to build a querry that finds duplicate entries of a feild named "assembly_position" and contains two counts, one count that finds the number of times an entry is duplicated. The second count has to count how many of the duplicate entries have been shipped by looking into a "load" feild checking if the field is not null.

so we take my table1:

AsemPos, load
A1, l1
A1, l2
A1, 
A3, l1
A2,
A2,
A3,

the result I'm looking for is:

AsemPos, Countod unique AP, Count of shipped
A1, 3, 2
A2, 2, 0
A3 ,2 ,1

I currently am able to get the unique entries as well as the first count with the following SQL statments

SELECT Sheet1.Assembly_position, Count(Sheet1.Assembly_position) AS CountOfAssembly_position
FROM Sheet1
GROUP BY Sheet1.Assembly_position;

What I need help with is the conditional count of the Items that have been shipped.

Thanks,
Jeff

[TheShabz]

You need 3 queries for this. First to pull the overal count, then to pull the count where load is not null, then to combine them. We'll call the first query qryTotalCount and the second qryLoadCount.

SELECT Sheet1.Assembly_position, Count(Sheet1.Assembly_position) AS CountOfAssembly_position
FROM Sheet1
GROUP BY Sheet1.Assembly_position;

SELECT Sheet1.Assembly_position, Count(Sheet1.Assembly_position) AS CountOfShipped
 FROM Sheet1
WHERE Sheet1.Load Is Not Null
 GROUP BY Sheet1.Assembly_position;

SELECT qryTotalCount.AsemPos, qryTotalCount.CountOfAssembly_position, qryLoadCount.CountOfShipped
FROM qryTotalCount LEFT JOIN qryLoadCount ON qryTotalCount.Assembly_position = qryLoadCount.Assembly_position;