Get the Highest Value in different Select SUM

Question

I want to get the highest value in my query

Select SUM(CASE WHEN Day='Monday' THEN 1 END) AS'Total Monday',
       SUM(CASE WHEN Day='Tuesday' THEN 1 END) AS'Total Tuesday'  
FROM tbl_sched 
WHERE teacherID='2014279384'

The Output would be TotalMonday ='1' and TotalTuesday ='2'
I need to get the highest value from the outputs which in this case is TotalTuesday=2

Answer 1

select max(daycnt) from
(Select SUM(CASE WHEN Day='Monday' THEN 1 END) AS daycnt
 from tbl_sched WHERE teacherID='2014279384'
union all
Select SUM(CASE WHEN Day='Tuesday' THEN 1 END) AS daycnt
 from tbl_sched WHERE teacherID='2014279384')

Answer 2

You can achieve this by using Max Function

Select MAX(SUM(CASE WHEN Day='Monday' THEN 1 END)) AS 'Total Monday',
   MAX(SUM(CASE WHEN Day='Tuesday' THEN 1 END)) AS 'Total Tuesday'  
FROM tbl_sched 
WHERE teacherID='2014279384'

Answer 3

If you need the max between many columns:

Something interesting in SQLServer 2008 and above

SELECT  (SELECT Max(v) 
FROM (VALUES ([Total Monday]), ([Total Tuesday]), ...) AS value(v)) as [MaxDate]
From
(   
    Select SUM(CASE WHEN Day='Monday' THEN 1 END) AS'Total Monday',
           SUM(CASE WHEN Day='Tuesday' THEN 1 END) AS'Total Tuesday'  
            ..........
    FROM tbl_sched 
    WHERE teacherID='2014279384'
    )a  

Another option:

SELECT Case When [Total Monday] > [Total Tuesday] then [Total Monday] else [Total Tuesday] End as maxvalue
FROM 
(   
    Select SUM(CASE WHEN Day='Monday' THEN 1 END) AS'Total Monday',
           SUM(CASE WHEN Day='Tuesday' THEN 1 END) AS'Total Tuesday'  
    FROM tbl_sched 
    WHERE teacherID='2014279384'
    )a

Answer 4

I'd say the query below is better in terms of performance and highlights the intention better, because basically we are just GROUPing by days and COUNTing the groups, we don't need CASE's or SUM's (in which case SQL Server will have to go over all the records of the selected teacher).

SELECT   TOP 1 Day, COUNT(*) AS Total
FROM     tbl_sched 
WHERE    teacherID='2014279384'
AND      Day IN ('Monday','Tuesday')
GROUP BY Day
ORDER BY Total DESC

Answer 5

You can just group by Day, sort by COUNT(*) DESC and get the top count:

SELECT TOP (1)
  TotalCount = COUNT(*)
FROM
  dbo.tbl_sched
WHERE
  teacherID = '2014279384'
GROUP BY
  Day
ORDER BY
  TotalCount DESC
;

You can also include Day into the output to return the day that had the topmost result.