CODEWITHSUNDEEP
sqlms-access
rel0aded0ne
rel0aded0ne

Reputation: 461

MS Access SQL - SELECT COUNT

I have a table with some entries and i need help with an sql command. The Table consists round about 50 entries with 6 columns. 

Table: tbl-planung

ID SID STATUS ... ... ... 
1  MDT Yes    ... ... ...
2  ABC Yes    ... ... ...
3  BLA NO     ... ... ...

I need a command which counts the total amount of entries in that table + the amount of entries with STATUS = Yes

Like:

TOTAL DONE
50    2

But my command returns

TOTAL DONE
50    50

SQL Command

SELECT Count([tbl-planung].Abgeschlossen) AS Total, 
       Count([tbl-planung].Abgeschlossen) AS Done
  FROM [tbl-planung]
 WHERE ((([tbl-planung].Abgeschlossen)=Yes));

Upvotes: 0

Views: 1350

Answers (2)

iDevlop
iDevlop

Reputation: 25252

Use SUM instead, without WHERE: 

SELECT Count([tbl-planung].*) AS Total, 
       -Sum([tbl-planung].Abgeschlossen) AS Done
  FROM [tbl-planung]

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Use a conditional SUM

SELECT Count(Abgeschlossen) AS Total, 
       sum(iif(Abgeschlossen = Yes, 1, 0)) AS Done
FROM [tbl-planung]

Upvotes: 2

Related Questions