Count records by group with 0 number for records out of criteria

Question

Hi,

Need a rope ;)) Take a look on example:

I have a table:

CREATE TABLE [dbo].[test3](
    [software] [varchar](50) NOT NULL,
    [result] [bit] NOT NULL,
    [computername] [varchar](50) NOT NULL
)

With data:

INSERT INTO test3 VALUES ('Adobe',1,'abc')
INSERT INTO test3 VALUES ('Office',1,'abc')
INSERT INTO test3 VALUES ('Adobe',0,'def')
INSERT INTO test3 VALUES ('Office',1,'def')

So after ordinary SELECT we have:

software                                           result computername
Office                                             1      abc
Adobe                                              1      abc
Office                                             1      123
Adobe                                              0      123

An NOW, my question. I would like to count Software quantity by software group. The "Result" column means: 0 not installed, 1 installed.

To count all installed (result=1) I can do simple:

SELECT
    Software
    ,COUNT(*) as Quantity
FROM
    test3
WHERE
    result = 1
GROUP BY
    software

BUT if I want to "reverse" the result and count how many computers from table have not software installed (result = 0) I will not see a results which I needs (for report).

After that:

SELECT
    Software
    ,COUNT(*) as Quantity
FROM
    test3
WHERE
    result = 0
GROUP BY
    software

I will get:

Software                                           Quantity
-------------------------------------------------- -----------
Adobe                                              1

Which means: "Only 1 Adobe is missed and rest of software is installed everywhere".

But I need for good look of report something like that:

Software                                           Quantity
-------------------------------------------------- -----------
Adobe                                              1
Office                                             0

Which means ;) "Only 1 Adobe installation is missed and 0 Office installation is missed."

I'm stuck with that :/ Plz help :)

Answer 1

Move the result check from the WHERE clause to a CASE:

SELECT
    Software
,   SUM(case when result = 0 then 1 else 0 end) as Missing
FROM     test3
GROUP BY software

That way, you will see software which has only successful installations.