hi there.. you are not being very polite.. instead of say please solve this, you should say can you please help me solving this?? we are not here to solve things, we are here to help you understand how things works, so in the future you can solve it by yourself...
anyway this query could be something like this
Code:
SELECT DISTINCT zzz_1.company, tabla2.total, derivedtbl_1.INSTOCK, TABLA3.OUTSTock
FROM (SELECT company, COUNT(Status) AS INSTOCK
FROM zzz
GROUP BY company, Status
HAVING (Status = 0)) AS derivedtbl_1 INNER JOIN
zzz AS zzz_1 ON derivedtbl_1.company = zzz_1.company INNER JOIN
(SELECT company, COUNT(company) AS total
FROM zzz AS zzz_2
GROUP BY company) AS tabla2 ON zzz_1.company = tabla2.company INNER JOIN
(SELECT company, COUNT(Status) AS OUTSTock
FROM zzz AS zzz_3
GROUP BY company, Status
HAVING (Status = 1)) AS TABLA3 ON zzz_1.company = TABLA3.company
this code works on SQL Server.. for oracle could be a little diferent...
the main table name is zzz, all the rest of the alias could be anything you want...
and the name of the company shouldn't be repeated in your main table, you should have there a numeric key and the name should be in another table....
HTH
Gonzalo
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Please provide solution
