how do i add filters to grouping id results

Question

I have the following query...

SELECT a.countryRegion, a.Stateprovince, SUM (soh.TotalDue) AS revenue,
CASE
WHEN GROUPING_ID(a.countryregion,a.stateprovince) = 0 THEN 'State/Province'
WHEN GROUPING_ID(a.countryregion,a.stateprovince) = 3 THEN 'Total'
WHEN GROUPING_ID(a.countryregion,a.stateprovince) = 1 THEN  'Country Total'
ELSE N'unknown'
END AS 'Level'
FROM saleslt.address AS a
JOIN saleslt.customeraddress AS ca
ON a.addressID = ca.addressID
JOIN saleslt.customer AS c
ON ca.customerID = c.customerID
JOIN salesLT.salesorderheader as soh 
ON c.customerID = soh.customerID
GROUP BY ROLLUP(a.countryregion, a.stateprovince)
ORDER BY  a.countryregion,a.stateprovince;

How do I get the row for Country to contain the value ‘United States Subtotal’, and the row for state/province to contain the value ‘California Subtotal’ for example?

The table includes about 8 distinct states/provinces and multiple countries.

Answer 1

To achieve the desired result, you can modify the CASE statement in your query. Currently, the CASE statement is using the GROUPING_ID function to determine the level of aggregation. To display 'United States Subtotal' for the country and 'California Subtotal' for the state/province, you need to add additional conditions to the CASE statement.

Here's an updated version of the query:
 

SELECT 

    a.countryRegion,

    a.Stateprovince,

    SUM(soh.TotalDue) AS revenue,

    CASE

        WHEN GROUPING_ID(a.countryregion, a.stateprovince) = 0 THEN 'State/Province'

        WHEN GROUPING_ID(a.countryregion, a.stateprovince) = 3 THEN 'Total'

        WHEN GROUPING_ID(a.countryregion, a.stateprovince) = 1 THEN 'Country Total'

        WHEN a.countryregion = 'United States' AND a.stateprovince IS NULL THEN 'United States Subtotal'

        WHEN a.countryregion = 'United States' AND a.stateprovince = 'California' THEN 'California Subtotal'

        ELSE N'unknown'

    END AS 'Level'

FROM 

    saleslt.address AS a

    JOIN saleslt.customeraddress AS ca ON a.addressID = ca.addressID

    JOIN saleslt.customer AS c ON ca.customerID = c.customerID

    JOIN salesLT.salesorderheader AS soh ON c.customerID = soh.customerID

GROUP BY 

    ROLLUP(a.countryregion, a.stateprovince)

ORDER BY 

    a.countryregion, a.stateprovince;

In the updated query, we added two new conditions to the CASE statement. The first condition checks if the country is 'United States' and the state/province is NULL, indicating the subtotal for the entire country. The second condition checks if the country is 'United States' and the state/province is 'California', indicating the subtotal for California. These conditions will populate the 'Level' column accordingly.

Answer 2

This can be achieved by filtering out the null  values by using group by a function like:

GROUP BY ROLLUP(a.countryregion, a.stateprovince)

And replacing null values with ‘subtotal’.

By implementing the below query like:

SELECT 

    coalesce(a.countryRegion,"Sub Total:") as countryRegion,

    coalesce(a.Stateprovince, "Sub Total:") as Stateprovince,

And after that use LEAD() and LAG() window function to extract the Stateprovince and country region from the previous line.

Interested to learn SQL, Check out the SQL training by Intellipaat with 24X7 support. 

Answer 3

To display 'United States Subtotal' for the country and 'California Subtotal' for the state/province in your query, you can modify the CASE statement. Add the following conditions after the existing ones:

WHEN a.countryregion = 'United States' AND a.stateprovince IS NULL THEN 'United States Subtotal'

WHEN a.countryregion = 'United States' AND a.stateprovince = 'California' THEN 'California Subtotal'

These conditions will assign the desired values to the 'Level' column based on the country and state/province.

Answer 4

Modify the CASE statement in your query by adding two new conditions:

WHEN a.countryregion = 'United States' AND a.stateprovince IS NULL THEN 'United States Subtotal'

WHEN a.countryregion = 'United States' AND a.stateprovince = 'California' THEN 'California Subtotal'

These conditions will assign the desired values to the 'Level' column.