Help me with query

Hi, Experts. I have a simple results of the query with two fields : status and COunt

Status                      Count of Days in Status
Under Review      228
Under Review      149
Under Review      139
Under Review      123
Under Review           74
Under Review           51
Under Review      43
Under Review      39
Under Review      35
Under Review      26
Under Review      26
Under Review      25
Under Review      20
Under Review      19
Under Review      17
Under Review      12
Under Review      8
Under Review      8
Under Review      7
==============================================
how to get those numbers using only one query

The status                                              : Under Review
Total Number of projects                       : 19
Total Number of projects  over 30 Days: 9
Total Number of projects over 50  Days ;6
Oldest                                                    : 228 days
Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
SELECT status,
         COUNT(*) total_number_of_projects,
         COUNT(switch (COUNT > 30, 1)) projects_over_30_days,
         COUNT(switch (COUNT > 50, 1)) projects_over_50_days,
         MAX(COUNT) oldest
    FROM yourtable
GROUP BY status


you could also use IIF(count>30,1,null)  instead of the switch
0
Roman FAuthor Commented:
I cannot get it work, only two of my numbers seems to be working...

SELECT
[Status Detail].[Project Status Text Key] AS [Project Status],
Count(*) AS total_number_of_projects,
Max([Status Detail].[Count of Days in Status]) AS Oldest
FROM [Status Detail]
WHERE ((([Status Detail].[Project Status Text Key])="Under Review"))
GROUP BY [Status Detail].[Project Status Text Key];
0
sdstuberCommented:
you didn't put the count(switch())  or count(iff)statements in


SELECT
[Status Detail].[Project Status Text Key] AS [Project Status],
Count(*) AS total_number_of_projects,
  COUNT(switch ([Count of Days in Status]> 30, 1)) projects_over_30_days,
  COUNT(switch ([Count of Days in Status]> 50, 1)) projects_over_50_days,
Max([Status Detail].[Count of Days in Status]) AS Oldest
FROM [Status Detail]
WHERE ((([Status Detail].[Project Status Text Key])="Under Review"))
GROUP BY [Status Detail].[Project Status Text Key];
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.