Count(*) with two criterias

[tran.loan]

Hi,
I'd like to do a query like this:

SELECT TABLE_ID, TABLE_NAME, COUNT_OUTPUT = (COUNT(*) FROM #TABLE WHERE
NUM_ID IN(27), (COUNT_EDIT = COUNT(*) FROM #TABLE WHERE NUM_ID NOT IN
(27))
FROM #TABLE
GROUP BY TABLE_ID, TABLE_NAME


Of course, that doesn't actually work. I just want a count of the temp
table with two different where clauses. Can someone help me?

 

['69 Camaro]

Try:

SELECT TABLE_ID, TABLE_NAME,
(SELECT COUNT(*)
FROM [#TABLE]
WHERE (NUM_ID = 27)) AS COUNT_OUTPUT,
(SELECT COUNT(*)
FROM [#TABLE]
WHERE (NUM_ID <> (27))) AS COUNT_EDIT
FROM [#TABLE]
GROUP BY TABLE_ID, TABLE_NAME;

Of course, COUNT_OUTPUT and COUNT_EDIT will be the same for every record
displayed.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

Select Table_ID, Table_Name, Sum(iif(Num_ID = 27,1,0)) as Count_Output,
Sum(IIf(Num_ID<>27,1,0)) as Count_Edit
From [#Table]
Group By Table_ID, Table_Name;

 

[Andy UK Access User Group]

SELECT TABLE_ID, TABLE_NAME, COUNT_OUTPUT = (COUNT(*) FROM #TABL
WHERE
NUM_ID IN(27), (COUNT_EDIT = COUNT(*) FROM #TABLE WHERE NUM_ID NOT I

(27))
FROM #TABLE
GROUP BY TABLE_ID, TABLE_NAME

You could possibly solve this by creating 3 queries, 2 of which ge
layered on top of the third

The first would do a group by id and count(*) with a where claus
NUM_ID IN(27) on the colum. The second would group by id with coun
(*) with a where clause NUM_ID <> 27

The third would have an outer join to the other 2 queries, linking o
ID