Embed a COUNT statement

[GerardMcL]

Hi,
I need to retrieve a set of results:
SELECT BatchID, FNum, FName FROM table WHERE ORDER = 2
But also:
SELECT Count(BatchID) As bCnt FROM table WHERE Order = 2

Can I stick this all into one sql statement (Im using a datareport designer in VB6 and need it condensed to one statement)

Any advice would be great or even alternate options I could use.
Cheers

 

[PHV]

You may try this:
SELECT BatchID, FNum, FName, (SELECT Count(BatchID) FROM table WHERE Order=2) As bCnt
FROM table
WHERE ORDER = 2


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244