Multiple Value Count(*) / Multiple Value Count (Criteria) 1

[tqeonline]

I'm working on trying to figure out how to divide a Count(Criteria) / Count(*) and am running into an issue.

Background: I have Values (Red, Green) and I need to get the percentage 'green' everyday to display on a bar graph.

One day works great but when it gets to two days it errors out.

Select
    Convert(varchar,[DateTime],101) AS 'Date',
    (
    
    select count([status])
    FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
    WHERE [DateTime] >= DATEADD(month,-1,GETDATE())
    AND [STATUS]='Green'

    )
    /cast(count([Status]) as float)*100 as Uptime
    FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
    WHERE [DateTime] >= DATEADD(month,-1,GETDATE())
    Group By Convert(varchar,[DateTime],101)

On the inner query i need to add

    Group By Convert(varchar,[DateTime],101)

But it gives me the error "The inner query returned multiple values" and it can't divide right.'

What I need it to do is say 9/8/2010 Green = 50/ total count (100) = 50% then 9/9/2010 = 75/100 = 75%

I need it to return the dates and percentages but am having trouble figuring out... Any ideas?

 

[bborissov]

Select Convert(varchar,[DateTime],101) AS Date,
       SUM(CASE WHEN Status = 'Green' THEN 1 ELSE 0 END)
        /cast(count([Status]) as float)*100 as Uptime
    FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
    WHERE [DateTime] >= DATEADD(month,-1,GETDATE())
    Group By Convert(varchar,[DateTime],101)

(not tested)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[tqeonline]

That worked perfect! Talk about quick response time!


Can you explain this part of the code to me?

SUM(CASE WHEN Status = 'Green' THEN 1 ELSE 0 END)

Is it saying that when the status is green add 1 to the sum?

How would the query look if i need to get the averages of "Green" and averages of "Yellow" for like a stacked bar chart?

It'd end up being 3 columns. Date - Green - Yellow

 

[TheBugSlayer]

SUM(CASE WHEN Status = 'Green' THEN 1 ELSE 0 END)
means add only Green to the total. In other words, add one for each Green.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[bborissov]

Yes, you need 3 columns, but then I prefer to have 4 columns:
1. Date,
2. Green total.
3. Yellow total.
4. All count.
Then you could get what you need in your front end.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.