[RESOLVED] SQL Query help - count of a condition?

[drag0n_45]

Hello all,
I have to tables in a database: Shipments and Packages
I'm trying to display some particular information, but just can't seem to get the SQL query right. Here's what I've got so far:

SELECT Shipments.ShipmentNumber, Shipments.ShipmentType, Shipments.Packaging, Count([Packages.Hazardous?]=True) AS RadPkgs, Count(Packages.ContainerNumber) AS ContainerCount, Sum(Packages.[Radiation(mR/hr)]) AS ContactTotal, Sum(Packages.TI) AS 1MTotal
FROM Packages INNER JOIN Shipments ON Packages.Shipment = Shipments.ShipmentNumber
GROUP BY Shipments.ShipmentNumber, Shipments.ShipmentType, Shipments.Packaging;

Unforunately, I can't modify the database fields, or I would to avoid the bad convention...

However, you can kind of see what I'm looking to do. My problem lies with the Count([Packages.Hazardous?]=True) AS RadPkgs part. I'm trying to retrieve the number of records where the [Packages.Hazardous?] field is true. I also tried using another SELECT statement in the where clause, but I couldn't find a way to associate the primary key with that subquery with the main query, so I'd just get a total count in the DB.
Any ideas?

[GaryMazzone]

What is the backend database? Access? SQL Server?

For SQL server set [Packages.Hazardous?]=1 and try that.

[si_the_geek]

You didn't mention which database system you are using, but assuming it is Access this would do it:

Sum(IIf([Packages.Hazardous?]=True,1,0)) AS RadPkgs

For SQL Server you would use Case (with different syntax) rather than Iif.

[drag0n_45]

worked like a charm, thanks!

It in indeed access. I didn't know access supported IIfs.
What other functions are supported? I could only find doct'n for SQL functions like COUNT, SUM, etc.

[si_the_geek]

The documentation is rather hard to get hold of, the FAQs contain links to the best info I've found on it (which is still lacking!).

Many VBA functions (such as Mid and Replace) are supported, but I've never seen a proper list. The best advice I can give is to type VBA. into a VB code window, and see what functions are listed - the chances are that they will work in Access SQL.

[drag0n_45]

But that's only with access SQL? What about MSSQL? Will those same functions work?

[GaryMazzone]

No they will not. I would actually recommend not using the Access working function and try and work it out in stright ANSI SQL.

Using Inner/Outer Joins, Sub Selects.

Your option might be an left outer join syntax on a sub select query in the From caluse (using a select as a table).

[si_the_geek]

Me too, but there are times where there isn't a standard way that would work, so you need to use DB-specific methods - and I'm fairly sure this (like most of the VBA functions) is one of them.

[drag0n_45]

Your option might be an left outer join syntax on a sub select query in the From caluse (using a select as a table).

I tried an Inner Join at one point and received the total count for all records in the database that matched my specs.

What would be the different between and outer left join in this case and an inner join?

I'm fairly experienced in SQL, but haven't written any kind of complex queries (with subselects or outer joins) like this before - most of my queries are simple inner joins, selects, updates, or complex where logic, so I'm having a bit of a hard time seeing how that would all come together in ANSI SQL.

[GaryMazzone]

The outer join would be on the base table (all records) and the select as a table in the from condition would have the Pk of the base table and the count of records that are hazard shipments. The Left outer will ensure that all records from the base table return even if no match is found on the Select table.