Insert Multiple Records with One Query and Get Count on the basis of GroupBY +SQL

SQL SERVER 2005

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

DECLARE@ProductDtls  TABLE(id INT, Department VARCHAR(30),Product VARCHAR(100))

--INSERT MULTIPLE RECORDS WITH ONE QUERY

INSERT INTO @ProductDtls (ID, Department, Product)

SELECT 1,'Electronics', 'Cameras & Video'UNION ALL

SELECT 2,'Electronics','Cameras & Video'UNION ALL

SELECT 3,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 4,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 5,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 6,'Electronics','Computers, Tablets & Laptops' UNION ALL

SELECT 7,'Electronics','Computers, Tablets & Laptops' UNION ALL

SELECT 8,'Fashion','Clothing & Accessories'UNION ALL

SELECT 9,'Fashion','Clothing & Accessories'UNION ALL

SELECT 10,'Fashion','Shoes' UNION ALL       

SELECT 11,'Fashion','Shoes' UNION ALL

SELECT 10,'Collectibles & Art','Antiques' UNION ALL

SELECT 11,'Collectibles & Art','Antiques' UNION ALL

SELECT 10,'Collectibles & Art','Antiques' UNION ALL

SELECT 11,'Collectibles & Art','Art' UNION ALL

SELECT 12,'Collectibles & Art','Art'

--SELECT ALL RECORDS

SELECT * FROM @ProductDtls

--GET COUNT ON THE BASIS OF GROUP RECORDS

SELECT Department, COUNT(T.Product) 'ProductCount' FROM

(SELECT DISTINCT Department,Product FROM @ProductDtls) T

GROUP BY Department