GROUP BY ALL in this case instructs to ignore the WHERE clause when doing the grouping, but apply WHERE clause for aggregate COUN function. AS the result - it returns all customers those exists in table [Customers] and table [Orders], but show count for USA customers only, for all other it is zero.

We can replace this with follow statement:

SELECT
    C.CustomerID,
    C.CompanyName,
    C.Country,
    COUNT(CASE WHEN C.Country='USA' THEN OrderID ELSE NULL END)  AS  NumberOfOrders
FROM
    dbo.Customers AS C
    INNER JOIN
    dbo.Orders AS OH
    ON C.CustomerID = OH.CustomerID
GROUP BY 
    C.CustomerID,
    C.CompanyName,
    C.Country;   
GO

Depending on the business rule there is one problem with GROUP BY ALL syntax, as well as proposed replacement syntax - if customer has no records in order table, it is not going to be shown in results. There are two customers with no records in Order tables (FISSA, PARIS). If we really would like to have all customers included into result set, here is another syntax:

    SELECT
    C.CustomerID,
    C.CompanyName,
    C.Country, OH.NumberOfOrders AS NumberOfOrders
    FROM dbo.Customers AS C
    LEFT OUTER JOIN
   (SELECT CustomerID, COUNT(OrderID) as NumberOfOrders
    FROM dbo.Orders
    WHERE ShipCountry='USA'
    GROUP BY CustomerID) OH
  ON C.CustomerID = OH.CustomerID

SELECT C.CustomerID, C.CompanyName, C.Country, SUM(CASE WHEN C.Country='USA' THEN 1 ELSE 0 END) AS NumberOfOrders FROM dbo.Customers AS C INNER JOIN dbo.Orders AS OH ON C.CustomerID = OH.CustomerID GROUP BY C.CustomerID, C.CompanyName, C.Country;
GO

A slightly different approach than what iazakharov already posted is to replace the conditional COUNT() with a conditional SUM().

But I disagree that missing customer values due to no corresponding rows in the orders table is a problem related to the GROUP BY ALL syntax in any way. It's driven by the INNER JOIN, which I would expect to be purposely. Therefore, I would not consider the left outer join approach.

It's also worth to remember that COUNT() together with GROUP BY ALL will return 0 (Zero) for values not covered by the original WHERE clause but will return NULL for aggregation functions like SUM(), MIN(), AVG().

SELECT
    C.CustomerID,
    C.CompanyName,
    C.Country,
    SUM(CASE WHEN C.Country='USA' THEN 1 ELSE 0 END)  AS  NumberOfOrders -- for a conditional COUNT()
-- ,SUM(CASE WHEN C.Country='USA' THEN  C.CustomerID ELSE NULL END)  -- for a conditional aggregation e.g. SUM(C.CustomerID)
FROM
    dbo.Customers AS C
    INNER JOIN
    dbo.Orders AS OH
    ON C.CustomerID = OH.CustomerID
GROUP BY 
    C.CustomerID,
    C.CompanyName,
    C.Country;   
GO

This query produces the same execution plan produced by the query optimizer from the original GROUP BY ALL construct:

SELECT  CustomerID = SQ2.Union1010,
        CompanyName = SQ2.Union1011,
        Country = SQ2.Union1012,
        number_of_orders = CONVERT(INT, COUNT_BIG(SQ2.Union1013), 0)
FROM    (
        SELECT  [Union1010] = SQ1.CustomerID,
                [Union1011] = SQ1.CompanyName,
                [Union1012] = SQ1.Country,
                [Union1013] = SQ1.Expr1004
        FROM    (
                SELECT  C.CustomerID,
                        C.CompanyName,
                        C.Country,
                        NULL AS [Expr1004]
                FROM    Northwind.dbo.Customers AS C
                JOIN    Northwind.dbo.Orders AS OH
                        ON  OH.CustomerID = C.CustomerID
                UNION   ALL
                SELECT  C.CustomerID,
                        C.CompanyName,
                        C.Country,
                        0 AS [Expr1009]
                FROM    Northwind.dbo.Customers AS C
                JOIN    Northwind.dbo.Orders AS OH
                        ON  OH.CustomerID = C.CustomerID
                WHERE   C.Country = N'USA'
                ) AS SQ1
        ) AS SQ2
GROUP   BY
        SQ2.Union1010,
        SQ2.Union1011,
        SQ2.Union1012
;

Lutz Mueller is right (and my earlier statement is wrong) with

"I disagree that missing customer values due to no corresponding rows in the orders table is a problem related to the GROUP BY ALL syntax in any way. It's driven by the INNER JOIN, which I would expect to be purposely. Therefore, I would not consider the left outer join approach."

Of course in initial query that is caused by INNER JOIN. LEFT JOIN will return 91 records.

-- duplicate post --

I would simply:
Remove the WHERE clause.
Change the GROUP BY ALL to GROUP BY.
Change the Count(*) to a conditional SUM().

USE Northwind;
GO

SELECT C.CustomerID, C.CompanyName, C.Country,
    SUM(CASE WHEN C.Country='USA' THEN 1 ELSE 0 END) AS numberoforders
FROM dbo.Customers AS C
    INNER JOIN dbo.Orders AS OH
        ON C.CustomerID = OH.CustomerID
GROUP BY C.CustomerID, C.CompanyName, C.Country;
GO

Nice one, Paul (SQLkiwi). It is fun seeing you translating the execution plan, but not a surprise, of the statement using GROUP BY ALL to a similar T-SQL query.

Take care, Alejandro

All answers are correct so far. As Luts pointed out, the two customers missing from the resultset are due to the type of join we are using.

Here is another way to simulate the GROUP BY ALL, in this case:

SELECT
    C.CustomerID,
    C.CompanyName,
    C.Country,
    COUNT(OH.OrderID) AS number_of_orders
FROM
    dbo.Customers AS C
    LEFT OUTER JOIN
    dbo.Orders AS OH
    ON C.CustomerID = OH.CustomerID
    AND C.Country = 'USA'
WHERE
    EXISTS (
    SELECT *
    FROM dbo.Orders AS OH1
    WHERE OH1.CustomerID = C.CustomerID
    )
GROUP BY
    C.CustomerID,
    C.CompanyName,
    C.Country;

As we have learned, there are different ways to achieve the same result without using GROUP BY ALL. Avoid using GROUP BY ALL, it will be removed in a future version of Microsoft SQL Server.

Cheers, AMB

check this

  select 
        b.CustomerID,
        b.CompanyName,
        b.Country, 
        MAX(n) as number_of_orders
    from
    (SELECT
        C.CustomerID,
        C.CompanyName,
        C.Country,
        ROW_NUMBER() over (partition by C.CustomerID,C.CompanyName,C.Country order by C.CustomerID,C.CompanyName,C.Country)n
    FROM
        dbo.Customers AS C
        INNER JOIN
        dbo.Orders AS OH
        ON C.CustomerID = OH.CustomerID
    WHERE
        C.Country = 'USA')b
    group by 
        b.CustomerID,
        b.CompanyName,
        b.Country