How can I get distinct counts with different where clauses on the same field? How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result?

I tried the following query:

SELECT DISTINCT
    A.DeviceFilter,
    (SELECT count(*) FROM DeviceLicense AS B WHERE A.DeviceFilter = B.DeviceFilter AND b.OrganizationId = 1001) 
FROM
    DeviceLicense AS A
GROUP BY
    A.DeviceFilter
It seems like you really want a PIVOT.

First, we'll create your DeviceLicense table, and populate some sample data:

CREATE TABLE dbo.DeviceLicense
(
    SINo int NOT NULL
    , DeviceFilter nvarchar(50) NULL
    , OrganizationID int NOT NULL
);

INSERT INTO dbo.DeviceLicense (SINo, DeviceFilter, OrganizationID)
VALUES (1, NULL, 1001)
    , (2, NULL, 1001)
    , (3, 'iPad', 1000)
    , (4, 'iPad', 1001)
    , (5, 'MACommon', 1000)
    , (6, 'MACommon', 1001);


This is the PIVOT statement.  As you can see below, the statement adds a PIVOT clause to a typical SELECT statement to allow rows to be transformed via an aggregate into columns.  

SELECT *
FROM (
    SELECT *
    FROM dbo.DeviceLicense
    ) src
PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;


The results of the select statement above:

+--------------+------+------+
| DeviceFilter | 1000 | 1001 |
+--------------+------+------+
| NULL         |    0 |    2 |
| iPad         |    1 |    1 |
| MACommon     |    1 |    1 |
+--------------+------+------+


I haven't added all your sample data, so the output is limited, but you can see there are columns for each OrganizationID, and rows for each DeviceFilter.  In future, know that it helps us give you a better answer if you provide the scripted DDL for the objects involved (the tables, etc), along with a table of data, not a screenshot.

Here, I'll attempt to show the PIVOT clause syntax in an understandable way:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;


The first obvious bit is the presence of the PIVOT keyword.  This tells SQL Server we're going to provide the following items:


a column from the main "src" SELECT query, wrapped in an aggregate, such as MAX(), SUM(), or in our case, COUNT().
The column headings we want, in the form of a list of the values present in a column from the "src" SELECT query.  In this case, the OrganizationID column contains the values 1000 and 1001, which we'd like displayed as headings.  The OrganizationID column might contain other values, such as 1002, but the way the PIVOT clause is written now, values from those rows will not be displayed.  In order to have 1002 values shown as another column, we'd need to define the pivot like this:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
        , [1002]  --note this creates an additional column in the output!
    )
) pvt;