CODEWITHSUNDEEP
sql-serverms-access-2007
MasterT
MasterT

Reputation: 623

Access 2007 SQL Count

i have a table like this 

Days taken
12
10
10
12
9
10
8
10

I want a query that out puts like this 

Type          | Count
Days over 10  |   2
Days under 10 |   6

i have this so far

SELECT Sum(IIf([table].[days taken]<=10,1,0)) AS [Days Under Ten], 
Sum(IIf([table].[days taken]>10,1,0)) AS [Days Over Ten]
FROM [table];

Which gives me this 

Days under ten | Days over ten
6              |       2

But its not what i want please help me.

Upvotes: 0

Views: 62

Answers (1)

Taryn
Taryn

Reputation: 247650

Your current query is very close to the correct version. You will want to use:

SELECT 
  IIF([days taken] <=10, 'Days under 10', 'Days over 10') as Type,
  count([days taken]) as Total
FROM yourtable
group by IIF([days taken] <=10, 'Days under 10', 'Days over 10')

You will see that I used the IIF to assign either the Days over 10 or Days under 10. Then you will add that same IIF to the GROUP BY to get the count()

Upvotes: 2

Related Questions