1

Having a table like this:

Example table

I want to get a result like this:

Result

Something like:

SELECT ParentId, COUNT(SomeValue = 1) [CountMin], COUNT(SomeValue > 1) [CountRest]
FROM MyTable
GROUP BY ParentId
  • Are you explicitly looking for the count of the value 1, or the count of whatever value is the minimum value? To put it another way, if you change all the 1 rows to 2, is the answer 0 | 7 or 4 | 3? – Aaron Bertrand Mar 2 '19 at 15:33
3

You can use SUM(CASE...)

SELECT
    ParentID,
    SUM(CASE WHEN SomeValue = 1 THEN 1 ELSE 0 END) AS CountMin,
    SUM(CASE WHEN SomeValue <> 1 THEN 1 ELSE 0 END) AS CountRest
FROM
    MyTable
WHERE
    ParentID = 123;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.