CODEWITHSUNDEEP
sql
Gerdes88
Gerdes88

Reputation: 25

SQL Query to get percentages of two selects?

I currently use two seperate Queries to recieve lists of total runs and lists of errors, so i use excel to divide these numbers to get percentages.

The problem is, that i use a subselect to get the errors, because i group the first select, and therefore cannot use the conditions in the first.

So my Query to get all runs is:

Select 
   Count(*) as All, year([US-Date]) as year, month([US-Date]) as month, day([US-Date]) as day
FROM 
   (Select 
      ROW_NUMBER() OVER (PARTITION BY Int_No ORDER BY Time desc) AS RowNumber, [US-Date]        
    FROM 
      dbo.Mydatabase        
    Where 
      [US-Date] between '2017-10-01' and '2018-03-01') AS a    
WHERE 
  a.RowNumber = 1    
GROUP BY  
  year([US-Date]), month([US-Date]), day([US-Date])    
ORDER BY  
year([US-Date]), month([US-Date]), day([US-Date])

which gives me a list of all testruns for each day.

then i use this Query to get the errors:

Select 
  Count(*) as fejlende, year([US-Date]) as år, 
  month([US-Date]) as måned, day([US-Date]) as dag    
From 
  (Select 
      ROW_NUMBER() OVER (PARTITION BY Int_No ORDER BY Time desc) AS RowNumber, [US-Date]        
   From 
      dbo.Mydatabase        
   Where 
      [US-Date] between '2017-10-01' and '2018-03-01' 
      and ErrorCode in 
          (Select 
             ErrorCode  from dbo.Mydatabase 
           Where 
             (ErrorCode like '2374' or ErrorCode like '2373' or ErrorCode like '2061'))) AS a       
WHERE 
      a.RowNumber = 1       
GROUP BY  
      year([US-Date]), month([US-Date]), day([US-Date])       
ORDER BY  
      year([US-Date]), month([US-Date]), day([US-Date])

So my question is: can i make one query that finds both lists, and divide them, so i dont have to put them into excel and so on :-)?

Upvotes: 1

Views: 66

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

You can use a CASE expression for this (I simplified the errorcode check):

Select  COUNT(*) as ALL
,       COUNT(CASE WHEN ErrorCode IN ('2374', '2373', '2061') THEN 1 END) AS fejlende
,       YEAR([US-Date]) as year
,       MONTH([US-Date]) as month
,       DAY([US-Date]) as day
from (
    Select ROW_NUMBER() OVER (PARTITION BY Int_No ORDER BY Time desc) AS RowNumber, [US-Date]
    From dbo.Mydatabase
    Where [US-Date] between '2017-10-01' and '2018-03-01') AS a
where a.RowNumber = 1
GROUP BY  year([US-Date]), month([US-Date]), day([US-Date])
ORDER BY  year([US-Date]), month([US-Date]), day([US-Date])

Upvotes: 1

Milney
Milney

Reputation: 6427

Something like this??

SELECT
    Count(*) as [Total], 
    SUM(CASE WHEN (ErrorCode like '2374' or ErrorCode like '2373' or ErrorCode like '2061') THEN 1 ELSE 0 END) AS Errors,
    year([US-Date]) as [Year], 
    month([US-Date]) as [Month], 
    day([US-Date]) as [Day]     
FROM dbo.Mydatabase       
WHERE ROW_NUMBER() OVER (PARTITION BY Int_No ORDER BY Time desc) = 1  
    AND [US-Date] between '2017-10-01' and '2018-03-01'     
GROUP BY  year([US-Date]), month([US-Date]), day([US-Date])
ORDER BY  year([US-Date]), month([US-Date]), day([US-Date])

Not really sure what your ROW_NUMBER is used for, but hopefully you get the idea and can adopt to your needs now you know the SUM(CASE WHEN) method?

Upvotes: 0

Related Questions