Calculate more than 1 sum of the same field in a SQL statement

[sv89]

Hi,

Is it possible to calculate more than 1 sum of the same field in a SQL statement/
I want a result like the following.

Ordered                 Done                       Pending
-----------------------------------------------------------
10                       8                              2

Where Ordered = Sum(OrderLineNumber)
Done = Sum(OrderLineNumber) where OrderLineNumber.status = 1
Pending = Sum(OrderLineNumber) where OrderLineNumber.status = 3

 

[Brianwarnock]

You can create fields such as

Pending: IIf(Status =3,[orderlinenumber],0)

and select Sum on the Total row


Brian

 

[sv89]

Oops...my bad.
Meant to do a Count. Not a Sum.

Thanks.

 

[Brianwarnock]

Pending: IIf(Status =3,1,0)

Total row is still Sum

Brian

 

[sv89]

Ah, I feel so stupid. I should have figured that on my own.

 

[Brianwarnock]

Don't be so hard on yourself, the forum is frequently asked how to do that sort of Count, and as a Uni lecturer once said
" Nothing is obvious to those who do not know"

Well actually he scrawled it in red ink over my lab test conclusions.

Brian