[RESOLVED] Select Count(*) syntax

Hello all,

Suppose I have a table in my database. This table, along with other fields contains the following fields: [manifest_id], [warehouse_id] and [process_status] (they are not primary keys). I want to get a count of all records that contain a specific mainfest_id AND warehouse_id, then within this subset of records, I want to find out how many records have the [process_status] field = 'A'.

In other words, how do I write a select query string that returns both the counts where the 1st count is the number of records that sastified one condition and the 2nd count is the number of records within the 1st record subset that sastified another condition?

My aim is to get a ratio of of records with process_status = 'A' from all records that have a specific manifest and warehouse id.

Any help is greatly appreciated.

what DBMS? The answer depends on the database type....

-tg

Oopps... Sorry that forgot to mention. It's IBM DB2 database sitting in an AS400 system and I'm using OleDb provider to interact with it.

ooooh..... sorry, outta my expertise there....
Not sure .... if it were SQL Server, I would have used the SUM() function, with Case statements to check for the criteria, and when they match, sum 1, else sum 0.... and so on....I'm not sure if DB2 allows the CASE structure though.

I suppose you could use two selects with the appropriate criteria, then join them back to a main query....

-tg

I don't have experience with it either... but perhaps a couple of thoughts will help you find what you need.

If this were Access, I'd suggest this:

Code:

---

SELECT [manifest_id], [warehouse_id], Sum(IIf([process_status] = 'A', 1, 0 )), Sum(1)
FROM tablename
GROUP BY [manifest_id], [warehouse_id]

---

and for SQL Server, like tg did, I'd suggest this:

Code:

---

SELECT [manifest_id], [warehouse_id], Sum(Case When [process_status] = 'A' Then 1 Else 0 End)), Sum(1)
FROM tablename
GROUP BY [manifest_id], [warehouse_id]

---

I suspect neither of these will work for you, but only due to the underlined sections - you just need to replace them with whatever kind of If/Case is supported.

Thank you both Techgnome and Si_the_geek. Using the Sum function with Case statement do work... And this is my query string

Code:

---

Dim sqlString As String = String.Format("Select " & _
                                "Sum(Case When (CCMANF = '{0}' And CCWHSE = '{1}') Then 1 Else 0 End) As TotalCount, " & _
                                "Sum(Case When (CCMANF = '{0}' And CCWHSE = '{1}' And CCPLAC = 'A') Then 1 Else 0 End) As ProcessedCount " & _
                                "From {2}", manifest, warehouse, cartonControlFile)

---