> I would like to select count (orderstatus) when new, ready, done,
> processing where company = 'x' 
> Important part  is I must get the no of new orders , no of ready orders
> and no. of done orders and no. of processing orders.
> 
> How in one query ?  help 
> 
> thanks in advance
> RnR
doing this in a single result record is tough, but this might be what you 
are looking for. if you absolutely have to have it in a single result 
record, then post your DDL for the relevant fields in the relevant tables 
and i'll work it out.

--begin query analyzer script

CREATE TABLE Orders
(
OrderID int PRIMARY KEY CLUSTERED, 
CompanyID VARCHAR(50) NOT NULL, 
OrderStatusID int NOT NULL
)

GO

CREATE TABLE OrderStatus
(
OrderStatusID INT PRIMARY KEY CLUSTERED,
OrderStatusDESC VARCHAR(20) NOT NULL
)

GO

INSERT INTO orders SELECT 1, 'x', 1
INSERT INTO orders SELECT 2, 'x', 3
INSERT INTO orders SELECT 3, 'x', 1
INSERT INTO orders SELECT 4, 'XYZZY', 1
INSERT INTO orders SELECT 5, 'x', 2
INSERT INTO orders SELECT 6, 'x', 3

INSERT INTO OrderStatus SELECT 1, 'New'
INSERT INTO OrderStatus SELECT 2, 'Ready'
INSERT INTO OrderStatus SELECT 3, 'Done'
INSERT INTO OrderStatus SELECT 4, 'Processing'

/* now that we have things defined, i believe this is what you were 
looking for */

SELECT 
	s.OrderStatusDESC, Count(DISTINCT o.OrderID) AS CountOfStatus
FROM 
	OrderStatus s LEFT JOIN
		Orders o
	ON o.OrderStatusID = s.OrderStatusID
WHERE
	o.CompanyID = 'x'
GROUP BY ALL
	s.OrderStatusDESC	
ORDER BY 
	s.OrderStatusDESC

/*in an application, you would probably use the status code instead of the 
description. i just used the desc here to make the resultset more human 
readable */