CODEWITHSUNDEEP
sqlsql-server
Nathan
Nathan

Reputation: 621

SQL MAX value of two sub queries

I have two queries and I want to get the maximum value of the two of them.

  MAX((SELECT COUNT(p.[ItemID]) FROM [dbo].[Table] p WHERE HasHuman=0),
      (SELECT COUNT(p.[ItemID]) FROM [dbo].[Table] p WHERE HasHuman=1))

Upvotes: 2

Views: 591

Answers (3)

dnoeth
dnoeth

Reputation: 60482

You can calculate both result in a single query and then apply TOP:

select top 1 
   HasHuman,
   COUNT(p.[ItemID]) as cnt
from [dbo].[Table]
group by HasHuman 
order by cnt desc

Upvotes: 2

etsa
etsa

Reputation: 5060

SELECT MAX(RC) 
 FROM (SELECT COUNT(p.ItemID) AS RC FROM dbo.[Table]
       WHERE HasHuman=0
       UNION 
       SELECT COUNT(p.ItemID) AS RC FROM dbo.[Table]
       WHERE HasHuman=1
       ) A

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

You could even do this in a single query:

SELECT
    CASE WHEN SUM(CASE WHEN HasHuman=0 THEN 1 ELSE 0 END) >
              SUM(CASE WHEN HasHuman=1 THEN 1 ELSE 0 END)
         THEN SUM(CASE WHEN HasHuman=0 THEN 1 ELSE 0 END)
         ELSE SUM(CASE WHEN HasHuman=1 THEN 1 ELSE 0 END) END
FROM [dbo].[Table]
WHERE ItemID IS NOT NULL    -- you were not counting NULLs

Upvotes: 2

Related Questions