Count Rows based on Condition

Use Sum and Case to count rows based on some condition. The sample query in under:

DECLARE @SumCase Table (
SubGroupID int, GroupID int, subGroupName nVarchar(25)
)
INSERT INTO @SumCase
SELECT 1, 2, 'Sugar' UNION ALL
SELECT 2, 2, 'Tea' UNION ALL
SELECT 3, 2, 'Salt' UNION ALL
SELECT 4, 3, 'Spices' UNION ALL
SELECT 5, 4, 'Pulses' UNION ALL
SELECT 6, 4, 'Rice' UNION ALL
SELECT 7, 4, 'Aata' UNION ALL
SELECT 8, 4, 'Maida'

SELECT
COUNT(*) as 'Total Sub Group',
SUM(CASE
WHEN GroupID = 2
THEN 1 ELSE 0 END) as 'Count'
FROM @SumCase